Formulation question

  • Thread starter Thread starter TJ Bartel
  • Start date Start date
T

TJ Bartel

I am trying to create a sliding scale for a payroll functionand would like
to create three different percent calculations on one cell based on the
amount. for instance if the cell value is 0-1200.00*45% if the value is over
1200.00*47% if value is over 2000.00 *50%
I have tried to do this for hours but having NO luck

thanks for any HELP (e-mail address removed)
 
If your value is in cell A1 then try
=IF(A1>2000,A1*50%,IF(A1>1200,A1*47%,A1*45%))
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
A more flexible solution would be to create a lookup table (D5:E7 in my
example):

0 0.45
1200 0.47
2000 0.5

then with the value to convert in H5, put this formula in I5:

=H5*(VLOOKUP(H5,$D$5:$E$7,2))

This gives you a >= relationship, not the > in John's formula. If you need
, you can subtract 0.01 from the values in D5:D7.
Click to expand...

This formula is an improvement because the cutoff values and percentages are
contained in a single table. If you need to adjust them you only need to
change the table, not every formula that relies on the values.

- Jon
 
Back
Top