Banding formula

[moe32]

hi, having an issue coming up with a formula to take to account banding with different prices.

Essentially i'd like a formula in cell I7 which is based on a manual number of transaction put in I5 and using the start & End range works out the potential cost.

It is an accumulated banding formula I am looking for.

Hopefully the excel file is self-explanatory.

Really struggling with this one.

[Bo_Ry]

Please try

=SUMPRODUCT(TEXT(I5-N(+C6:C10),"0;\0;0")*(D7:D11-N(+D6:D10)))

[moe32]

Please try

=SUMPRODUCT(TEXT(I5-N(+C6:C10),"0;\0;0")*(D7:D11-N(+D6:D10)))

great stuff! thank you!

I managed to figure out a way to get the answer but its horrible compared to what you have provided;-

=IF(AND(I5>=B7,$I$5<=C7),($I$5*D7),IF(AND($I$5>=B8,$I$5<=C8),((C7-B7)*D7)+(($I$5-B8)*D8)+D8,IF(AND($I$5>=B9,$I$5<=C9),((C7-B7)*D7)+((C8-B8)*D8)+(($I$5-B9)*D9)+D9+D8,IF(AND($I$5>=B10,$I$5<=C10),((C7-B7)*D7)+((C8-B8)*D8)+((C9-B9)*D9)+(($I$5-B10)*D10)+D10+D9+D8,((C7-B7)*D7)+((C8-B8)*D8)+((C9-B9)*D9)+((C10-B10)*D10)+(($I$5-C10)*D11)+D8+D9+D10))))

[Bob Phillips]

=SUMPRODUCT(TEXT(I5-N(+C6:C10),"0;\0;0")*(D7:D11-N(+D6:D10)))

Why did you add the TEXT function in there? It seems to work fine without it

=SUMPRODUCT((I5-N(+C6:C10))*(D7:D11-N(+D6:D10)))

[moe32]

Why did you add the TEXT function in there? It seems to work fine without it

=SUMPRODUCT((I5-N(+C6:C10))*(D7:D11-N(+D6:D10)))

thanks. im now trying to figure out how this formula works but key thing is, it works!

[Bob Phillips]

The question is to the responder, but FYI, it is essentially a variable rate formula.

(I5-N(+C6:C10)) builds an array of the number of transactions reduced by each band, aka 5000,4000,33000,2000,1000.

The other part, (D7:D11-N(+D6:D10)), builds takes the start prices (0,1.0.05,0.04,0.03,0.02,0.01) minus the previous start (text,0,1.0.05,0.04,0.03,0.02) to give an array of price differentials (0.1, -0.05, -0.01, -0.01, -0.01).

The two arrays are multiplied and summed with SUMPRODUCT to get the result.

As I said, it seems to work fine without the TEXT function.

[Bo_Ry]

Please try with transactions 1000,2000,… and compare the result with and without TEXT function.

[Bob Phillips]

Ah, I see, the text gets rid of the negative values. Thanks man, I should have seen that.