Slightly harder/complicated Excel formula

[Atazala]

Hi there;

I have a small problem, I need to condense this table of formulas into one calculation and this is beyond my meagre skills, I build the table to calculate some discount bands, basically applying a rate to the cell highlighted (red) taking the figure apart portioning it into bands and applying rates to those bands putting it back together and giving me the total discount for that value across all bands, its currently set at 700 the final figure (highlighted in orange is what I'm hunting for)

This works a treat for one-off calculations but I need it to do this same thing 240 times and have them all dynamically change. If this is too hard I'll move this into VBA forums



[TABLE="width: 1109"]
<tbody>[TR]
[TD]Band[/TD]
[TD]Rate[/TD]
[TD]Bands[/TD]
[TD][/TD]
[TD]$ in band[/TD]
[TD]$ discount[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]=IF(C10>C5,C5,C10)[/TD]
[TD]=IF(D4>0,D4,0)[/TD]
[TD]=D4*(1-B4)[/TD]
[TD]=E4/C$10[/TD]
[TD]=G4*(1-B4)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]0.7[/TD]
[TD]100.001[/TD]
[TD]=IF(C$10>C6, (C6-C5),(C$10-C5))[/TD]
[TD]=IF(D5>0,D5,0)[/TD]
[TD]=E5*(1-B5)[/TD]
[TD]=E5/C$10[/TD]
[TD]=G5*(1-B5)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]0.6[/TD]
[TD]300.001[/TD]
[TD]=IF(C$10>C7, (C7-C6),(C$10-C6))[/TD]
[TD]=IF(D6>0,D6,0)[/TD]
[TD]=E6*(1-B6)[/TD]
[TD]=E6/C$10[/TD]
[TD]=G6*(1-B6)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]0.5[/TD]
[TD]600.001[/TD]
[TD]=C10-C7[/TD]
[TD]=IF(D7>0,D7,0)[/TD]
[TD]=E7*(1-B7)[/TD]
[TD]=E7/C$10[/TD]
[TD]=G7*(1-B7)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SI[/TD]
[TD][/TD]
[TD]700[/TD]
[TD]=SUM(D4:D7)[/TD]
[TD]=SUM(E4:E9)[/TD]
[TD]=SUM(F4:F9)[/TD]
[TD]=SUM(G4:G7)[/TD]
[TD]=SUM(H4:H7)[/TD]
[/TR]
</tbody>[/TABLE]

 

[ranman256]

instead of formulas, you could run a macro, to scan the list and produce the values.
(stops you from typing in formulas)