ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Charts and Charting in Excel (https://www.excelbanter.com/charts-charting-excel/)
-   -   Formulation question (https://www.excelbanter.com/charts-charting-excel/133323-formulation-question.html)

TJ Bartel

Formulation question
 
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



John

Formulation question
 
If your value is in cell A1 then try
=IF(A12000,A1*50%,IF(A11200,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)


"TJ Bartel" wrote:

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




Jon Peltier

Formulation question
 
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.


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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"john" wrote in message
...
If your value is in cell A1 then try
=IF(A12000,A1*50%,IF(A11200,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)


"TJ Bartel" wrote:

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







All times are GMT +1. The time now is 01:49 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com