Nested IF statement - with both less than and greater than

[kidnos]

Hi all - I am trying to make this formula a little easier - it currently works but it is kind of long. Does anyone know of a way to shorten it up? Oh, I have to have the ISNUMBER check in it... Thank you

=IF(ISNUMBER($O168/AF168),(IF($O168/AF168<0,"-",(IF($O168/AF168>50,"-", ($O168/AF168))))),"-")

 

[SkipVought]

Hi,

First off, in this expression, $O168/AF168 if EITHER is not a number yer up the creek!

What are you actually tyring to do? I'd guess...
[tt]
=IF(AF168<>0,(IF($O168/AF168<0,"-",(IF($O168/AF168>50,"-", ($O168/AF168))))),"-")
[/tt]
cuz, any ZERO or EMPTY value in column AF is a #DIV0! error.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

it looks like you're trying to get a + if the result is positive or a - if the result is negative.
You can actually do this with formatting
Format->Cells->Number->Custom
change the contents of the formatting box to "+;-" without the quotes.

Then, your formula should be =IF(ISERROR($O168/AF168),"",$O168/AF168)

Humorously enough, even though the content of the cells is either a + or a -, the cell still has a value! You can still use it.

 

[SkipVought]

Here we go GUESSING at what the OP intended.

The only POSITIVE test is >50, certainly not POSITIVE VALUES. Looks more like the bounds of some requirement.

So EXACTLY WHAT is the OP trying to accomplish???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Well we are told that the original formula works. So, accepting that, the formula can be simplified using the AND function.

=IF([red]AND(NOT(ISNUMBER($O168/AF168), $O168/AF168<0, $O168/AF168>50)[/red],”-“, $O168/AF168)



Gavin

 

[lionelhill]

I find "or" a bit more readable:
=IF(ISNUMBER($O168/AF168), IF(OR($O168/AF168<0, $O168/AF168>50), "-", $O168/AF168), "-")

But Gruuuu has a point. You could, if you wanted, use conditional formatting to format values less than zero to "" (which will display - because the sign will be added to the non-existent number displya) and any value greater than 50 to "-". The value, if it exists, will then be available should it be useful any time. Of course the calculation must be arranged to do the isnumber check and return a dummy value if no number. It's an approach that might be useful in some circumstances.

 

[Gavona]

Quite right Lionelhill in that in my example the AND should have been OR and I missed a bracket.
=IF(OR(NOT(ISNUMBER($O168/AF168)), $O168/AF168<0, $O168/AF168>50),"-", $O168/AF168)

I agree that NOT(ISNUMBER($O168/AF168)) is clumsy/hard to read but so are nested IFs. Maybe the OP would clarify the requirement but the result of dividing one thing by another thing will either be numeric or an error. So why not use ISERROR?
=IF([RED]OR(ISERROR($O168/AF168), $O168/AF168<0, $O168/AF168>50)[/RED],"-", $O168/AF168)

Gavin

 

[kidnos]

Thanks everyone!