IF with Summary Sheet Information

dharberts

=IF(Jan2 >= 1, [Baseline = 1-250 Devices Shipped]#, IF(Jan2 <= 250, },[Baseline = 1-250 Devices Shipped]#), IF(Jan2 >= 251, [Tier 1 = 251-500 Devices Shipped]#, IF(Jan2 <= 500, [Tier 1 = 251-500 Devices Shipped]#), IF(Jan2 >= 501, [Tier 2 = 501-1,000 Devices Shipped]#, IF(Jan2 <= 1000, [Tier 2 = 501-1,000 Devices Shipped]#), IF(Jan2 >= 1001, [Tier 3 = 1,001-1,500 Devices Shipped]#, IF(Jan2 <= 1500,[Tier 3 = 1,001-1,500 Devices Shipped]#), IF(Jan2 >= 1501, [Tier 4 = 1,501-2,000 Devices Shipped]#, IF(Jan2 <= 2000,[Tier 4 = 1,501-2,000 Devices Shipped]#), IF(Jan2 >= 2001, [Tier 5 = 2,001-2,500 Devices Shipped]#, IF(Jan2 <= 2500, [Tier 5 = 2,001-2,500 Devices Shipped]#), IF(Jan2 >= 2501, [Tier 6 = 2,501-3,000 Devices Shipped]#, IF(Jan2 <= 3000, [Tier 6 = 2,501-3,000 Devices Shipped]#, 0)))))))))

Returning

#UNPARSEABLE

Paul Newcome

You have some closing parenthesis tucked in where they don't belong as well as an extra piece there after the second IF.

All closing parenthesis for the IF functions should come at the very end of the formula. The way you transitioned from the first IF to the second IF is how it should be done for all of them. You also have some extra characters there after the logical statement portion of the second IF.

dharberts

OK got all that figured out so thank you but now it stops at the first tier. I think its because its grater that 1, I think I need an OR so it would be Greater than or Equal to 1 or Less Than or Equal to 250? Does that make sense?

Paul Newcome

Nested IF formulas will stop on the first true value. This means they will skip any false arguments that come before the true. You can leverage this to avoid using AND and OR statements by structuring your nested IFs.

=IF(This<= 1, 0, IF(This<= 10, 10, IF(This<= 15, 15)))

The above will output zero for anything less than or equal to 1, ten for anything between 2 and 10, and fifteen for anything between 11 and 15.

dharberts

Ok did this and getting #UNPARSEABLE

=IF(Jan4 = 1, [Baseline = 1-250 Devices Shipped]#, IF(Jan4 <= 250, [Baseline = 1-250 Devices Shipped]#, IF(Jan4 = 251, [Tier 1 = 251-500 Devices Shipped]#, IF(J4 <= 500, [Tier 1 = 251-500 Devices Shipped]#, IF(J4 = 501, [Tier 2 = 501-1000 Devices Shipped]#, IF(J4 <= 1000, [Tier 2 = 501-1000 Devices Shipped]#))))))

dharberts

Neverminded I figured it out :)