Formula that changes answer cell based on numeric value in reference cell

Try this nested IF function,

=IF(C8<=99,M2,IF(C8<=999,M3,IF(C8<=3999,M4,IF(C8<=7999,M5,M6))))

Let me break it down so you can see what's happening there;


The IF function works as follows: (also this is the color code I'm using for the rest of this post)
=IF(logical_test,value_if_true,value_if_false)

Logical Test = Any test that returns a TRUE or FALSE value.

So =if(100>50,"true","false")
Since 100 is greater than 50, excel views that formula as
=if(TRUE,"true","false")
Since it is a TRUE, the Value_if_true option is selected

Nesting them as I have above is:

If the value is Less than or Equal to 99, use the value in M2 which is .16
if it is NOT less than or equal to 99, it comes back as FALSE and the value_if_false section is selected, which is another test that states that the value is less than or equal to 999, if true it will return a $.12. If false, it goes to the next if, and so on.

If none of them are true, it will return the .06 value, because there is no upper limit on that bracket I don't have to put another IF, because if all of the previous tests are FALSE, the only possible reason is that the number is greater than 7999, which would be 8000+


Hope this helps!