Formula help

[Alex D]

Trying to write a formula, but having a really hard time. I want to do the following...

If the first cell says, Commercial, then I want it to look at the second cell (# there now is 32) and if that value is less than 30, give me a 1 if not a 0, and if the first cell is NOT commercial, then look at 3rd cell (# there now is 29) and if that value is less than 30 give me a 1 if not a 0. on top of this, i want an iferror to show me blank.....

Commercial

32​

29​

 

[shinigamilight]

i want an iferror to show me blank

Idk what this means, if the cell is blank you want to show blank?

=IF(A2<>"",IF(A2="Commercial",IF(B2<30,1,0),IF(C2<30,1,0)),"")

 

[Alex D]

Idk what this means, if the cell is blank you want to show blank?

=IF(A2<>"",IF(A2="Commercial",IF(B2<30,1,0),IF(C2<30,1,0)),"")

I want a blank, if the formula produces an error...the first cell will ALWAYS be either COMMERCIAL or something else, never ever blank.

 

[xlhelp15]

I want a blank, if the formula produces an error...the first cell will ALWAYS be either COMMERCIAL or something else, never ever blank.

The one which is given by shinigamilight does the same !

 

[shinigamilight]

=IFERROR(IF(A2="Commercial",IF(B2<30,1,0),IF(C2<30,1,0)),"")

 

[Alex D]

=IFERROR(IF(A2="Commercial",IF(B2<30,1,0),IF(C2<30,1,0)),"")

I just thought of something else, as I put that formula on my sheet, something else came up.....so this formula you gave me is perfect, thank you, but how do I get a "blank" result if either B2 and or C2 is blank?

 

[Sufiyan97]

See if this helps

=IFERROR(IF(A2="Commercial",IF(OR(B2="",C2=""),"",IF(B2<30,1,0)),IF(C2<30,1,0)),"")

 

[Alex D]

See if this helps

=IFERROR(IF(A2="Commercial",IF(OR(B2="",C2=""),"",IF(B2<30,1,0)),IF(C2<30,1,0)),"")

Appreciate the reply, but it didn't solve for what I am trying to do.

 

[Alex D]

Appreciate the reply, but it didn't solve for what I am trying to do.

Here is what I am after.....

First cell says: Commercial or Residential (never blank)
Second cell is a #, let's pretend it is 32
Third cell is a #, let's pretend it is 25

I want my formula, to look up the first cell, and if it's Commercial, go to the second cell, and if that value is less than 30, then give me a 1, if not a 0, but also look to see if second cell is blank, and if so, return the blank

If the first cell is not commercial, go to the third cell and if that value is less than 30, then give me a 1, if not a 0, but also look to see if third cell is blank, and if so, return the blank

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula Help.....
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.