A similar solution can be found on Chip Pearson's site. I find them very
ingenious, but difficult to remember.
So I always use this UDF:
================================================================
Function PercPerSegment(Amount As Double, Table As Range) As Double
' 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 witin 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
================================================================
I suppose I could have used the mcgimpsey/pearson Worksheet technique in the
UDF as well. I'll look into that some time...
Kind Regards,
Niek Otten
Microsoft MVP - Excel
Peo Sjoblom said:
