Several options:
1. You can use my UDF, listed at the end of this post.
In a worksheet, press ALT-F11 to get you to the Visual Basic Editor.
InsertModule. Paste the code of the UDF into the module.
Press ALT-F11 to return to the worksheet. You can use the function there as if it were a built-in function.
2. Look he
http://www.mcgimpsey.com/excel/variablerate.html
The UDF:
' ==================================================
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
' ================================================== ==
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
"Natalie John" wrote in message ...
| Here are the requirements
|
| I need one answer with different parameters within one formula
|
| If amount is less than or equal to 100 then answer will be Zero
| If amount is more than 100 but less than or equal to 150 then answer will be
| 8% of the amount exceeding 100
| If amount is more than 150 but less than or equal to 300 then answer will be
| 25 + 12% of the amount exceeding 150
| If amount is more than 300 but less than or equal to 400 then answer will be
| 50 + 20% of the amount exceeding 300
| If amount is more than 400 but less than or equal to 700 then answer will be
| 75 + 25% of the amount exceeding 400
| If amount is more than 700 then answer will be 100 + 35% of the amount
| exceeding 700