Multiple options- answer in single cel

[Peterest]

Hi Guys,
What a very helpful bunch of guys.
I would appreciate help on this one


Cells A1,A2 and C2 contain the numbers 5, 10 and 15 respectively.
These numbers relate to the price of something which is conditional on the
number in B1. e.g if B1 is 249 or less A1 is 5, if B1 is between 249 and 499
then A2 is 10, if 500 or higher than 500 C2 is 15

Cell B1 can contains any number -- say, for example 250

Cell C1 should display the answer to cell B1multiplied by either 5, 10 or
15, depending on the number in B1.

Help please!!

Cell C1 is the answer to the formu
Peterest

 

[Bernard Liengme]

A1: =IF(B1<=249,5,0)
A2: =IF(AND(B1>249,B1<500),10,0)
C2: =IF(B1>=500,15,0) (should this be in A3 ?)
C1: =B1*(A1+A2+C2)

If you do not like the zeros, use
A1: =IF(B1<=249,5,"") ---- that two double quotes in places of
the zero
Do same with other IF formulas
Replace C1 formula by =B1*SUM((A1:A2,C2)) because SUM can handle a formula
that returns a 'null-string'

And if you want to dispense with the A1, A2, C2 formulas and use only C1:
=B1*((B1<250)*5+(B1>249)*(B1<500)*10+(B1>=500)*15)

best wishes

 

[Eduardo]

Hi,
Try

=IF(B1<250,(B1*A1),IF(B1<500,(B1*A2),IF(B1>=500,(B1*C2))))