Problem with If Formula

microhunt

Board Regular
Joined
Aug 14, 2017
Messages
57
Office Version
  1. 2021
Platform
  1. Windows
I am using the following formula in cell A5. I would like if N5 is blank then A5 also return blank. If N5 is 0 or is greater than zero then equals K5. I am nearly there but cannot get the results I want

Here is the conditions for the formula.

Excel Formula:
=IF(OR(T5="FALSE",B5>0,N5>=0.01),K5,IF(OR(T5="TRUE",O5>0),K5,""))


If T4 is false and B4 or N4 is greater than zero or equal to then output "K4" If T4 is false and B4 and N4 or both blank then output nothing just blank.

If T4 is True and N4 or O4 is greater than zero or equal to then output "K4" If T4 is True and N4 and O4 or both blank then output nothing just blank.

Any help would be appreciated
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
If T4 is false and B4 or N4 is greater than zero or equal to then output "K4" If T4 is false and B4 and N4 or both blank then output nothing just blank.

If T4 is True and N4 or O4 is greater than zero or equal to then output "K4" Iç

AND(T4="False", OR( B4>=0, N4>=0) ) OR AND(T4="True", OR( N4>=0, O4>=0) )

So that can be re-written as IF ( OR( AND(T4="False", OR( B4>=0, N4>=0) ), AND(T4="True", OR( N4>=0, O4>=0) )) , K4,

T4 is false and B4 and N4 or both blank
So is this B4 OR N4 is blank, or do BOTH B4 & N4 need to be Blank
Same question for
T4 is false and B4 and N4 or both blank

BUT first test if
IF ( OR( AND(T4="False", OR( B4>=0, N4>=0) ), AND(T4="True", OR( N4>=0, O4>=0) )) , K4, "FALSE")
work OK
 
Upvote 0
Thank you for taking an interest.

I tried both your formulas.

Excel Formula:
=IF(OR(AND(T4="False",OR(B4>=0,N4>=0)),AND(T4="True",OR(N4>=0,O4>=0))),K4)

Excel Formula:
=IF(OR(AND(T4="False",OR(B4>=0,N4>=0)),AND(T4="True",OR(N4>=0,O4>=0))),K4,"FALSE")


All I get is false in A4 and nothing happens?

Thank you
 
Upvote 0
in which case the TRUE Section is NOT working
=OR(AND(T4="False",OR(B4>=0,N4>=0)),AND(T4="True",OR(N4>=0,O4>=0)))

So its worth checking that
bit by bit
in a cell put
=AND(T4="False",OR(B4>=0,N4>=0))
in a different cell put
=AND(T4="True",OR(N4>=0,O4>=0))
what do you get in those cells TRUE or FALSE

Make sure B4 or N4 is greater than 0 and not blank for 1st test
Make sure N4 or O4 is greater than 0 and not blank for 2nd test

otherwise , need to see the spreadsheet loaded here, either via a share on dropbox/onedrive
OR use the 2XLBB as linked to in my signature
 
Upvote 0
No both cells are still false with formulas in different cells.

This formula works except one problem. If you enter 0 in vell N4 then nothing is displayed. I would like if you enter 0 in N4 then k4 is displayed.

Excel Formula:
=IF(OR(T4="FALSE",B4>0,N4>=0.01),K4,IF(OR(T4="TRUE",O4>0),K4,""))
 
Upvote 0
Ok, thanks, thats the reason then
so without a sample - this may take a longer exchange of posts
in a cell put =T4="False"
in another cell put T4="True"
what result do you get
 
Upvote 0
=T4=FALSE in a cell and toggle the checkbox in S4 which is linked to T4 I can toggle between true and false by clicking and unclicking, no problems
 
Upvote 0
OK then. if a logical TRUE / FALSE you need to remove the ""
IF ( OR( AND(T4=FALSE, OR( B4>=0, N4>=0) ), AND(T4=TRUE, OR( N4>=0, O4>=0) )) , K4, "Test does not meet")
 
Upvote 0
Thank you Wayne, we are very close. Don't ask me why but when I added quotes around the FALSE and TRUE. the formula started to work. However I would like if N4 or B4 had a value of 0 or greater then K4 would be displayed. At the moment it has to be both cells with 0 or above to make it work.

Thank you so much

Excel Formula:
=IF(OR(AND(T4=FALSE,OR( B4<=0,N4<=0)),AND(T4="TRUE",OR(N4<=0,O4<=0))),"private",K4)

Step by Step

So if T4 is false and Cells B4 and N4 having nothing in them then blank. If cell T4 is False and either cell B4 or N4 have a zero or greater then display =K4

So if T4 is True and Cells O4 and N4 having nothing in them then blank. If cell T4 is TRUE and either cell O4 or N4 have a zero or greater then display =K4

Very, Very Close. I am impressed with you.

Another Step Further

Excel Formula:
=IF(OR(AND(T4=FALSE,AND( B4<=0,N4<=0)),AND(T4="TRUE",OR(N4<=0,O4<=0))),"private",K4)

Okay when it T4 is FALSE. If you enter a an amount greater than 0 into either cell B4 or Cell N4 it works as expected by displaying K4. However if you enter zero into either B4 or N4 it still stays display "private".
 
Last edited:
Upvote 0
so try the formula
=AND(T4=FALSE,OR(B4>=0,N4>=0))
What is the result - test with T4, B4 & N4

=AND(T4=TRUE,OR(N4>=0,O4>=0))
What is the result - test with T4, N4 & O4

However,
=IF(OR(AND(T4=FALSE,OR( B4<=0,N4<=0)),AND(T4=TRUE,OR(N4<=0,O4<=0))),"private",K4)
This is saying B4 or N4 is zero or lower - so it will be FALSE - K4 if B4 or N4 is greater than zero - NOT equal to
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top