How do I make a function for < and >

[SeeStation]

Cell a1 contains the result of a function (19.94)

how can I make a function for B1
b1="High" if a1 is <20
b1="Low" if a1 is >26
b1="Within Margin" if 26<a1>20
b1="N/A" if false or if a1 contains "#VALUE" as a result of it's function
not being fullfilled?

Thanks!

 

[zhang]

select B1 Then write
=if(A1<20,"High",if(a1<26,"Within Margin",if(a1>26,"Low","N/A")))

 

[zhang]

=and(A1>26,A1>20)

select B1 Then write
=if(A1<20,"High",if(a1<26,"Within Margin",if(a1>26,"Low","N/A")))

 

[ShaneDevenshire]

Hi,

It seems counter intuitive to indicate values below 20 as high and values
above 26 as low, but if this is what you are doing then you might use:

=IF(A1<20,"High",IF(A1>26,"Low",IF(ISERR(A1),NA(),"Within Margin")))

Note: I am checking A1 for any error, not just a VALUE error, if that's not
a problem then this should do.

 

[ShaneDevenshire]

Hi,

It seems counter intuitive to call somethng over 26 low and something under
20 high? But if that is so then you could use

=IF(A1<20,"High",IF(A1>26,"Low",IF(ISERR(A1),NA(),"Within Margin")))

Notice I am checking for any error in A1 not just VALUE errors, but I double
that matters.

 

[SeeStation]

Thanks for your assistance - we are almost there: Let me explain - I am
working with pricing margins. If the margin is too high, my price is too
low. If the margin is too low, my price is too high. If my price is just
right it will fall into a resonable margin range that will allow my wholesale
customers to sell my product for retail value and still be competitive in the
market. OK - I got one more issue with this example and it revolves around
the value in a1.

a1=((E21-E20)/E21)*100
if E21 contains a vlaue of "Not Available" then a1 displays "#VALUE!"
and in turn, a1 displays the same thing as well as b1

 

[SeeStation]

Thanks for your assistance - we are almost there: Let me explain - I am
working with pricing margins. If the margin is too high, my price is too
low. If the margin is too low, my price is too high. If my price is just
right it will fall into a resonable margin range that will allow my wholesale
customers to sell my product for retail value and still be competitive in the
market. OK - I got one more issue with this example and it revolves around
the value in a1.

a1=((E21-E20)/E21)*100
if E21 contains a vlaue of "Not Available" then a1 displays "#VALUE!"
and in turn, a1 displays the same thing as well as b1

 

[Pete_UK]

You could change your formula in A1 to this:

=IF(ISERROR((E21-E20)/E21*100),"",(E21-E20)/E21*100)

This will show a blank in A1 instead of an error message.

Hope this helps.

Pete

 

[SeeStation]

Thanks Pete_UK - it wasn't quite what I was looking for, but you got me in
the right direction. Just in case anyone else was following this thread,
here is my working solution:

a1=IF(ISERROR((E21-E20)/E21*100),"Not Available",(E21-E20)/E21*100)

b1=IF(F21<20,"Dealer Cost is High",IF(F21="Not Available","Not
Available",IF(F21>26,"Dealer Cost is Low",IF(ISERR(F21),NA(),"Within
Margin"))))

Under Pete_UK's example the ,"",( in the function for cell a1 was applied in
b1 as <20 which displayed "Dealer Cost is Low". Now if the cells E21 or A1
read 'Not Available', so does b1.

You guys pointed me in the right direction and got me 99% of the way there.
Thanks a bunch!

 

[Pete_UK]

You're welcome. Good to see that you have taken the suggestions
further and come up with your own solutions.

Pete