Formulation

  • Thread starter Thread starter Windrick Smith
  • Start date Start date
W

Windrick Smith

Here's my problem, I have a spreadsheet and in column C I
have a dollar amount. In column D I want Microsoft excel
to take that dollar amount from column C and if that
amount is 1.00-10.00 I want it to multiply by 100%, and
if the amount is 10.01 to 25.00, I want it to multiply
the amount by 60%, and if the amount is over 25.00 I want
that amount to be multiply by 40%. I don't know to make
this work. Please help.
 
Windrick,

Try the following formula.
=C1*(IF(C1<=10,1,IF(C1<=25,0.6,0.4)))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
There is a lot of missing detail here. What about values <1? What
about values between 10.00 and 10.01, ...

Put the following formula in D1 and copy it down.

=IF(C1>25,C1*40%,IF(C1>10,C1*60%,IF(C1<1,"oops",C1)))

It ingores the possibility of balues between non-touching boundaries,
but does warn you of values <1. If values <1 are impossible, then you
could simplify to

=IF(C1>25,C1*40%,IF(C1>10,C1*60%,C1))

If you want to make decisions on values that have been rounded to
2-decimal places (so that 10.004 would be treated as 10.00 but 10.005
would be treated as 10.01) then the preceeding formulas would become

=IF(ROUND(C1,2)>25,C1*40%,IF(ROUND(C1,2)>10,C1*60%,IF(ROUND(C1,2)<1,"oops",C1)))
and
=IF(ROUND(C1,2)>25,C1*40%,IF(ROUND(C1,2)>10,C1*60%,C1))

You could also round the cell values in the calculation if that was your
intent.

Jerry
 
Back
Top