Calculate cumulative total based on given number in cell

Hi,

If you put the top number from the range into A2,A3,A4 and the prices in B2,B3,B4, with the number of dogs in A6, then put this in the cell where you want the result:

Formula:

Please Login or Register  to view this content.

Here's a file with it working: Cumulative total _ for channguyen.xlsx
On the second sheet, I've done it with named ranges, as that might make it easier to understand what's going on. That reads like this:

Formula:

Please Login or Register  to view this content.

Hope that does what you want.

One way would be to create a pricing chart with the cumulative prices pre-calculated. This table could then be used with Vlookup to return the price for the number of dogs. This would allow you to easily change the prices.

Formula:

Please Login or Register  to view this content.

	M	N
1	Dogs	Price
2	1	10
3	2	20
4	3	30
5	4	40
6	5	50
7	6	55
8	7	60
9	8	65
10	9	70
11	10	75
12	11	78
13	12	81
14	13	84
15	14	87
16	15	90
17	16	93
18	17	96
19	18	99
20	19	102
21	20	105

If you use the max price for each sales level then this will work.

Formula:

Please Login or Register  to view this content.

Try this...

	A	B
1	5	10
2	10	5
3	20	3
4
5	12	81

B5=SUM(A5*3,IF(A5>$A$2,$A$2,$A$5)*2,IF(A5>$A$1,$A$1,A5)*5)