VBA to calculate charges

[Guest]

Hi,
I'm new to Excel programming and would like some tip on how to calculate a
simple charge using vba.

Scenario:
For 100 unit and below, use $0.10 per unit
For 101 to 200 unit, use $0.15 per unit
For 201 and above, use $0.20 per unit

If I set cell A1 "Unit" to 150, then first 100=$10, plus next 50unit=$7.50

So cell B1 to show $7.50 as "Charges".

How do I code using vba?

Or is there a easier way, e.g using formula in worksheet without using vba
at all?

Thank you.

 

[Karthik Bhat - Bangalore]

Hi Choo

A simple formula will take care of your requirement.
Suppose you have the units in cell A1 then enter the following formula
in cell B1

=IF(A1<0,0,(IF(A1<=100,A1*0.1,IF(A1<=200,10+(A1-100)*0.15,25+(A1-200)*0.2))))

And by the way the result of 150 units will be 17.5 and not 7.5. Or is
there something that I have missed...

Thanks
Karthik Bhat
Bangalore

 

[Guest]

Hi Karthik,
Thanks for the formula. And yes the result should be 17.50. Typo error
Lets say if I want to create a simple table for the rates like:

a1 b1
1 <=100 0.1
2 101 to 200 0.15
3 201 and above 0.20

How should the formula look like?

 

[Niek Otten]

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

 

[Niek Otten]

You can also use this User Defined Function:

' -----------------------------------------------------------------------------
Function PricePerSlice(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 two-column wide Price or Tax% table (vertical)
' Fisrt column the threshold values, second column the corresponding
' prices or percentages
' 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
PricePerSlice = SumSoFar
End Function
' -----------------------------------------------------------------------------

 

[George Nicholson]

You could set a table up so your solution uses lookup values. One advantage
is that editing the table may be easier that editing the formula (and you
know it will need to be edited at some point in time). Also easier to play
"what-if?".

Units Charge@ Less
0 0.10 0.00
101 0.15 5.00
201 0.20 15.00

Charges = (NumOfUnits x Dlookup( NumOfUnits, MyTable, 2))-
Dlookup(NumOfUnits,MyTable,3)

Charge for 201 units = (201 * 0.20) - 15.00 = 25.20

HTH

 

[Guest]

Hi,
Thank you so much for all your valuable input. I find them all useful and
workable. Thanks again.

 

[Roger Govier]

Hi

One way
With your data table in A1:B3 and the Value entered in cell C1
=MIN($A$1,C1)*$B$1+MAX(0,C1-$A$1)*$B$2+MAX(0,C1-$A$3)*($B$3-$B$2)


Regards

Roger Govier