Problem with If Formula

microhunt

New Member
Joined
Aug 14, 2017
Messages
39
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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
 

microhunt

New Member
Joined
Aug 14, 2017
Messages
39
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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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
 

microhunt

New Member
Joined
Aug 14, 2017
Messages
39

ADVERTISEMENT

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,""))
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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
 

microhunt

New Member
Joined
Aug 14, 2017
Messages
39

ADVERTISEMENT

=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
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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")
 

microhunt

New Member
Joined
Aug 14, 2017
Messages
39
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:

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,410
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,051
Members
414,281
Latest member
Engjamal2021

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
Top