a little advanced formula, can you help? Please

  • Thread starter Thread starter uptwospeed
  • Start date Start date
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.
 
Just another way, not necessarily better (though it handles negative
numbers):

=A1*(0.4 + 0.05*((A1>=850)+(A1>1000)))
 
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
 
Back
Top