Formula based on price bands

[ziadmannan]

Hi all,

I'm looking for a way to do a calculation based on price bands where a value needs to be calculated based on several bands:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Transaction Bands[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]0.2[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]0.15[/TD]
[/TR]
[TR]
[TD]50000[/TD]
[TD]0.1[/TD]
[/TR]
[TR]
[TD]1000000000[/TD]
[TD]0.05[/TD]
[/TR]
</tbody>[/TABLE]

Based on the bands above, if the number of transactions is 80000 then the way the total amount needs to be calculated is:

First 10000 are charged at 0.2 = 2000
Between 10000 and 20000 @ 0.15 = 1500
Between 20000 and 50000 @ 0.1 = 3000
Between 50000 and 1000000000 @ 0.05 (applies to 30000) = 150

Total = 6650

Any help would be much appreciated. I tried searching for this but couldn't find anything. I'm probably not searching with the right keywords.

Thanks in advance
Ziad

 

[Special-K99]

Try here:
Allocated Pricing

 

[ziadmannan]

Hi Special-K99, thanks for that. I'm having a look at it and trying to get my head around it, it's quite complex!

 

[Special-K99]

Yep sorry I had a go at it but couldnt work it out myself, so I took the easy way out and sent you a link

I've seen this posted on various Excel boards many times so you might be able to locate a simpler or easier explained solution by Googling a bit.

 

[Marcelo Branco]

Hi all,

I'm looking for a way to do a calculation based on price bands where a value needs to be calculated based on several bands:

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Transaction Bands
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]10000
[/TD]
[TD]0.2
[/TD]
[/TR]
[TR]
[TD]20000
[/TD]
[TD]0.15
[/TD]
[/TR]
[TR]
[TD]50000
[/TD]
[TD]0.1
[/TD]
[/TR]
[TR]
[TD]1000000000
[/TD]
[TD]0.05
[/TD]
[/TR]
</TBODY>[/TABLE]

Based on the bands above, if the number of transactions is 80000 then the way the total amount needs to be calculated is:

First 10000 are charged at 0.2 = 2000
Between 10000 and 20000 @ 0.15 = 1500
Between 20000 and 50000 @ 0.1 = 3000
Between 50000 and 1000000000 @ 0.05 (applies to 30000) = 150

Total = 6650

Any help would be much appreciated. I tried searching for this but couldn't find anything. I'm probably not searching with the right keywords.

Thanks in advance
Ziad

Hi Ziad,

I think that if amount is 80,000, then the correct result is 8,000 not 6,650 - because 30,000 x 0.05 = 1,500 not 150

Take a look at
McGimpsey & Associates : Excel : Using SUMPRODUCT() with variable rates

Using the suggested solution in your case


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Transaction Bands​

[/td][td]

Rate​

[/td][td]

Differential Rate​

[/td][td] [/td][td]

Amount​

[/td][td]

Result​

[/td][/tr]
[tr][td]

2​

[/td][td]

0​

[/td][td]

0,2​

[/td][td]

0,2​

[/td][td] [/td][td]

80000​

[/td][td]

8000​

[/td][/tr]
[tr][td]

3​

[/td][td]

10000​

[/td][td]

0,15​

[/td][td]

-0,05​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

4​

[/td][td]

20000​

[/td][td]

0,1​

[/td][td]

-0,05​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td]

50000​

[/td][td]

0,05​

[/td][td]

-0,05​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]


Formula in C2 (Differential Rates) copied down
=B2-N(B1)

Formula in F2 (Result)
=SUMPRODUCT(--(E2>A2:A5),E2-A2:A5,C2:C5)

Hope this helps

M.

 

[ziadmannan]

Thanks Marcelo. That does seem simpler but I'm not familiar with the sumproduct function so will need to look into this further but it looks promising.

Also yes the total should be 8000 as you pointed out.

Thanks again.

 

[Marcelo Branco]

Hi Ziad,

For an excellent explanation about SUMPRODUCT take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

M.