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

**dtinman** · 08-28-2014, 12:09 PM

Hey all,

I'm not the most experienced with excel, but have been elected the office "computer guy". I'm trying to create a worksheet where we plug in three numbers and everything else is handled by formulas for calculating pricing on a product we make in our office.

I am running into trouble when I have D8 reference C8 for a numeric value, then show the proper price based off of a scale (eg. 1-99 = $.16, 100-999 = $.14, 1000-3999 = $.12 etc.) Where C8's number decides the price shown in D8.

I'm doing something similar in H8 based off of the numbers in A8 and B8 as well, but that may take us a while longer to come up with.

I've really only used =sum type formulas before, and I feel like an =if formula would work, but I'm having trouble writing one with multiple answers.

Thanks for the help in advance!

Tinman

**Speshul** · 08-28-2014, 12:17 PM

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!

**dtinman** · 08-28-2014, 12:20 PM

It worked! I appreciate the help!

**dtinman** · 08-28-2014, 12:37 PM

Makes sense now that I've seen it broken down like that! My brain doesn't work in a very linear way so stuff like this can get me bogged down really quick!