a little advanced formula, can you help? Please

[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.

 

[Peo Sjoblom]

Assuming you can't have negative numbers

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

 

[J.E. McGimpsey]

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

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

 

[Max]

Assuming a slight tweak to your 3rd criteria:

IF A1 is greater than 1000, multiple it by .50

to mean

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

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

 

[uptwospeed]

Thanks Dude,
You are the greatest...I am the worst.

Peace Out, do