You are viewing limited content. For full access, please sign in.

Question

Question

how to modify IF statement to not have 0 calculated

asked on January 8, 2020

I have the below calculation working, however I know that I want 0 to default in.  How do I modify my first IF statement to not include 0.  Essentially I want 1-9 to show up as 25 and >9 to show as 10 with 0 being 0?

=IF(HowManyAddlLots<10, 25, IF(HowManyAddlLots>9, 10, 0))

0 0

Replies

replied on January 8, 2020 Show version history

You can flip your evaluation around the other way (i.e., start with the high range and go down).

=IF(HowManyAddlLots>9, 10, IF(HowManyAddlLots>0, 25, 0))

What this says is,

If > 9 then 10

Else

   If > 0 (i.e., 1-9) then 25

   Else 0

Because it checks for > 9 first, nothing will make it to the second IF unless it is 9 or less, so you don't need to check for a less than 10 value, you can just check if it is greater than 0 at that point.

 

You could also evaluate from low to high, the key is just that you don't need to check for < 10, you only need to check for > 0 and > 9.

For example,

=IF(HowManyAddlLots>0, IF(HowManyAddlLots>9, 10, 25), 0)

Here it is doing the following instead

If > 0 then

      If > 9 then 10

      Else 25

Else 0

1 0
replied on January 8, 2020

Thank you Jason for your help.  That works perfectly.  

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.