Looking for VBA function/macro for a rate table calculation

[coryhouston]

I'm looking for help in creating a function or macro that will calculate a fee based on a tiered fee table.

In reference to the attached file, I have a defined fee table that is used to calculate a fee based on volume discounts. Given a table or list of volumes, I would like to call a function or macro to calculate the appropriate fee. I currently accomplish this using nested IF statements, but this method is too cumbersome because I frequently need to add/delete tiers and replicate the table/calculation in different parts of the spreadsheet.

As such I would like to take the step of making the calculation a "procedure call" via a function or macro. I realize I may have to modify or replicate the function or macro to accommodate additional or reduced tiering, but I can live with that.

While I have done some Basic programming in my past, I'm new to VBA in the Excel environment.

[stnkynts]

Please Login or Register  to view this content.

[coryhouston]

Thanks, but I see I was unclear in what I was trying to achieve. The pricing is such that the client is assessed a fee of $8 on each of first 500 items, $7 on the subsequent 500 items, and so on. So if the client has 2,000 items, he's paying (500 x $8 + 500 x $7 + 1,000 x $6) for a total of $13,500 and an average cost of $6.75.

I'm looking for a custom formula to calculate the total.

[stnkynts]

Try this:

Please Login or Register  to view this content.