Progressive Addition Formula

[Robert Moore]

I have a spreadsheet that totals complaints per contractor each day.
The contractors get charged a fee per complaint. I want this fee to be
progressive, meaning, for example they would get charged $3.00 for the
first complaint, $4.00 for the second, $5.00 for the third, etc. The
number of complaints on any given day would usually be in the range from
1 to 10 per day. So if they had 5 complaints it would be 1*3+1*4+1+1*5+
etc up to 5 or a total charge of $25.00. Obviously another way to do
this would be to simply add 3+4+5+6+7. But I do want to be able to
alter the amount I charge them per complaint so it won't always be this
progression. For example, they may get their first complaint for free
and then the charges start, or perhaps the first 3 for free and then the
charges start...

Can you help me with a formula

 

[Anne Troy]

This is the perfect time to use a vlookup. Create a charge amount table for
complaints on sheet2. Column A: Number of complaints, Column B: Price.
Select the table and give it a name: prices
Then you can use vlookup: =vlookup(B2,prices,2,false)
If you do it this way, you can change your prices without changing all your
formulas.
Here's a tutorial:
http://www.officearticles.com/tutorials/order_form_or_invoice_form_in_microsoft_excel.htm
*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

Hi Robert,

Assuming the data range in C2:C13 as follows:

Com Penalty

1 0
2 0
3 0
4 6
5 7
6 8
7 9
8 10
9 11
10 12

Enter the number of complaints in C16 and the following array formula
(Ctrl+Shift+Enter) in cell D16

SUM(IF(C4:C13<=C16,D413))

Regards,