Can this formula be simplified?

[Drew Terp]

=IF(($C$6-C29)>400,4,IF(($C$6-C29)>350,3.5,IF(($C$6-C29)>300,3,IF(($C$6-C29)>250,2.5,IF(($C$6-C29)>200,2,IF(($C$6-C29)>100,1.5,IF(($C$6-C29)>50,1,0.5)))))))

I'm creating a worksheet to assist my chefs in their fish order. C6 refers to how many reservations we have that evening. C29 is the amount of reservations that are parties (they don't count toward total reservations for ordering fish - yes, I could pull this aside to simplify). Each breakdown is checking the amount of reservations minus parties, and then suggesting a set amount of cases of fish to order. Is there an easier way to go about this?

 

[dreid1011]

Give this a try:

=LOOKUP($C$6-C29,{0,51,101,201,251,301,351,401},{0.5,1,1.5,2,2.5,3,3.5,4})

 

[Drew Terp]

=LOOKUP($C$6-C29,{0,51,101,201,251,301,351,401},{0.5,1,1.5,2,2.5,3,3.5,4})

When I plugged it in, it returned a value of 4 instead of the desired value of 2.5.

 

[Michael M]

What data is in C6 and C29 to get the result of 2.5 ??
IF C6 = 424 and c29 = 19 then that gives you 405 which will return 4 ???
Also, when using the IF formula in this case, you should always start at the lowest value and work up,not the other way round.
In your example formula, if the value was greater than 400, then all cases of the formula would be true !!

 

[Alex Blakenburg]

How about:

=IF($C$6-C29>450,4,FLOOR(($C$6-C29)/100-0.0001,0.5))

which can also be done this way:

=LET(diff,$C$6-C29,IF(diff>450,4,FLOOR((diff)/100-0.0001,0.5)))

 

[Drew Terp]

What data is in C6 and C29 to get the result of 2.5 ??
IF C6 = 424 and c29 = 19 then that gives you 405 which will return 4 ???
Also, when using the IF formula in this case, you should always start at the lowest value and work up,not the other way round.
In your example formula, if the value was greater than 400, then all cases of the formula would be true !!

I posted the original formula where the balance against was C29, but when I reposted with the screen shot I had moved the values to C10. C6=424, and C10=127, then the value comes back as 2.5.

I haven't had any issues with returning multiple values for anything working down the formula, but I see the wisdom in starting lowest and going to highest.

 

[Drew Terp]

How about:

=IF($C$6-C29>450,4,FLOOR(($C$6-C29)/100-0.0001,0.5))

which can also be done this way:

=LET(diff,$C$6-C29,IF(diff>450,4,FLOOR((diff)/100-0.0001,0.5)))

These are both great! Thank you! Where can I go to learn why this works? I'd like to be able to understand and not just utilize it.

 

[Michael M]

If you use the Formula Tab and select Evaluate formula. You can step through the formula one step ata time and see how it works.

 

[Eric W]

This will shorten Alex's formula a bit more:

=MIN(4,FLOOR(($C$6-C29)/100-0.0001,0.5))

Both Alex's idea, and the trick I just suggested, are both the result of lots of experience and experimentation, and seeing how others solve problems. Like Michael said, try running the formula through the Evaluate Formula tool, with different input values.