I have a simple spreadsheet for calculating salaries of workers of a small
business. I have a long nested IF expression for calculating the income tax
component. It is working but it is too long and not easy to edit. Is there
any way out?
Assumption:
Income Rate
First $150 0%
Next $150 5%
Next $300 10%
Next $2,100 12%
Next $2,700 15%
Exceeding $6,000 30%

Could anyone be of help?
Thank you

Darkwah

Hi Darkwah,

Look he

http://www.mcgimpsey.com/excel/variablerate.html

Or use this UDF. I include instructions for implementing UDFs in case you're new to them.

' ================================================== =======
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ================================================== =======


================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Darkwah" wrote in message ...
|I have a simple spreadsheet for calculating salaries of workers of a small
| business. I have a long nested IF expression for calculating the income tax
| component. It is working but it is too long and not easy to edit. Is there
| any way out?
| Assumption:
| Income Rate
| First $150 0%
| Next $150 5%
| Next $300 10%
| Next $2,100 12%
| Next $2,700 15%
| Exceeding $6,000 30%
|
| Could anyone be of help?
| Thank you
|
| Darkwah

this would be a good spot for a lookup
either as an array or as a table
=LOOKUP(A1,{0,151,301,601,2701,6001},{0,0.05,0.1,0 .12,0.15,0.3})
you might want to use 150.01 etc depending on your actual cutoover point,

"Darkwah" wrote:

I have a simple spreadsheet for calculating salaries of workers of a small
business. I have a long nested IF expression for calculating the income tax
component. It is working but it is too long and not easy to edit. Is there
any way out?
Assumption:
Income Rate
First $150 0%
Next $150 5%
Next $300 10%
Next $2,100 12%
Next $2,700 15%
Exceeding $6,000 30%

Could anyone be of help?
Thank you

Darkwah

Hi


I think you have a mistake with your table, as there is a "hole" between
5400 and 6000 where no tax would be paid.
Cumulatively until the end of the 12% band it is 2700, and the 15% band
needs to be 3300 to reach the final band of 6000

You could do it all in one cell with the following formula
=MIN(A1-150,150)*5%+MAX(0,MIN(A1-300,300))*10%+
MAX(0,MIN(A1-600,2100))*12%+MAX(0,MIN(A1-2700,3300))*15%+
MAX(0,A1-6000)*30%

Or if you wanted to see it set out as a table then
in cells E4 to E9 enter 150,300,600,2700,6000, Over 6000
in cells G4 to G9 enter 0%,5%,10%,12%,15%,30%
in cell F4 enter
=MIN(E4,$A$1)-F3
and copy down to cell F9
in cell H4 enter
=F4*G4 and copy down to H9

In F10 enter = SUM(F4:F9)
in H10 = SUM(H4:H9)

In both cases enter salary figure in cell A1
--
Regards

Roger Govier


"Darkwah" wrote in message
...
I have a simple spreadsheet for calculating salaries of workers of a
small
business. I have a long nested IF expression for calculating the
income tax
component. It is working but it is too long and not easy to edit. Is
there
any way out?
Assumption:
Income Rate
First $150 0%
Next $150 5%
Next $300 10%
Next $2,100 12%
Next $2,700 15%
Exceeding $6,000 30%

Could anyone be of help?
Thank you

Darkwah

Hello Niek,
I am glad it is working perfectly.

Darkwah

"Niek Otten" wrote:

Hi Darkwah,

Look he

http://www.mcgimpsey.com/excel/variablerate.html

Or use this UDF. I include instructions for implementing UDFs in case you're new to them.

' ================================================== =======
Function PercPerSegment(Amount As Double, Table As Range) As Double
' Niek Otten, March 31, 2006

' Progressive pricing
' First argument is the quantity to be priced
' or the amount to be taxed
' Second argument is the Price or Tax% table (vertical)
' Make sure both ends of the table are correct;
' usually you start with zero and the corresponding price or %
' Any value should be found within the limits of the table, so
' if the top slice is infinite, then use
' something like 99999999999999999 as threshold
' and =NA() as corresponding value

Dim StillLeft As Double
Dim AmountThisSlice As Double
Dim SumSoFar As Double
Dim Counter As Long

StillLeft = Amount

For Counter = 1 To Table.Rows.Count - 1
AmountThisSlice = Application.Min(StillLeft, Table(Counter + 1, 1) _
- Table(Counter, 1))
SumSoFar = SumSoFar + AmountThisSlice * Table(Counter, 2)
StillLeft = StillLeft - AmountThisSlice
Next
PercPerSegment = SumSoFar
End Function
' ================================================== =======


================================================
Pasting a User Defined Function (UDF)
Niek Otten, March 31, 2006

If you find a VBA function on the Internet or somebody mails you one, and you don't know how to implement it, follow these
steps:

Select all the text of the function.
CTRL+C (that is, press and hold down the CTRL key, press C, release both). This a shortcut for Copy.
Go to Excel. Press ALT+F11 (same method: press and hold the ALT key, press the F11 key and release both). You are now in the
Visual Basic Editor (VBE).
From the menu bar, choose InsertModule. There should now be a blank module sheet in front of you. Click in it and then
press CTRL+V (same method.). This a shortcut for Paste. You should now see the text of the function in the Module.
Press ALT+F11 again to return to your Excel worksheet.
You should now be able to use the function as if it were a built-in function of Excel, like =SUM(..)
================================================



--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Darkwah" wrote in message ...
|I have a simple spreadsheet for calculating salaries of workers of a small
| business. I have a long nested IF expression for calculating the income tax
| component. It is working but it is too long and not easy to edit. Is there
| any way out?
| Assumption:
| Income Rate
| First $150 0%
| Next $150 5%
| Next $300 10%
| Next $2,100 12%
| Next $2,700 15%
| Exceeding $6,000 30%
|
| Could anyone be of help?
| Thank you
|
| Darkwah