a little advanced formula, can you help? Please

U

uptwospeed

I am trying to make a formula to include all the following information.

IF A1 is less than 850 then multiple it by .40
IF A1 is 850 through 999.99 multiple it by .45
IF A1 is greater than 1000, multiple it by .50

Any help would be great. I have a huge head ache trying to figure it
out. Thanks in advance.
 
P

Peo Sjoblom

Assuming you can't have negative numbers

=A1*LOOKUP(A1,{0;850;1000},{0.4;0.45;0.5})
 
J

J.E. McGimpsey

Just another way, not necessarily better (though it handles negative
numbers):

=A1*(0.4 + 0.05*((A1>=850)+(A1>1000)))
 
M

Max

Assuming a slight tweak to your 3rd criteria:
IF A1 is greater than 1000, multiple it by .50
Click to expand...

to mean
IF A1 is greater than or equal to 1000, multiple it by .50
Click to expand...


Try say, in B1 : =IF(A1<850,A1*0.4,IF(AND(A1>=850,A1<1000),A1*0.45,A1*0.5))


Perhaps consider also using a VLOOKUP instead for such situations
(more readable, easier to maintain, not bound by IF() nesting limits...)

Set-up a reference table somewhere, say in G1:H3

0..............0.4
850..........0.45
1000........0.5

Name the range G1:H3 as say: MyTable

Put in C1 : =VLOOKUP(A1,MyTable,2,TRUE)*A1


Both the IF() in B1 and the VLOOKUP() in C1 will return the same results
 
Top