Rating discrete chunks of a total amount

[Cklessa]

Hi - thanks for this very interesting

I tried this but am still having issues. Could you please check my logic

I have put together a string of these to make the formula;

=MIN(B14,C1-B1)*B2
+MIN(D1-C1,MAX(0,B14-(C1-B1))*C2
+MIN(E1-D1,MAX(0,B14-(D1-C1)*D2)))
+MIN(F1-E1,MAX(0,B14-(E1-D1)*E2))
+MIN(G1-F1,MAX(0,B14-(F1-E1)*F2))
+MIN(H1-G1,MAX(0,B14-(G1-F1)*G2
+MIN(I1-H1,MAX(0,B14-(H1-G1)*H2)))
+MIN(J1-I1,MAX(0,B14-(I1-H1)*I2))
+MIN(K1-J1,MAX(0,B14-(J1-I1)*J2))
+MAX(0,B14-(K1-J1))*K2)

Based on the following data;

[TABLE="width: 560"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 48pt" span=7 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="class: xl2004, width: 682, bgcolor: transparent, colspan: 10"]Users[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Region[/TD]
[TD="bgcolor: transparent, align: right"]0[/TD]
[TD="bgcolor: transparent, align: right"]5000[/TD]
[TD="bgcolor: transparent, align: right"]10000[/TD]
[TD="bgcolor: transparent, align: right"]20000[/TD]
[TD="bgcolor: transparent, align: right"]40000[/TD]
[TD="bgcolor: transparent, align: right"]80000[/TD]
[TD="bgcolor: transparent, align: right"]160000[/TD]
[TD="bgcolor: transparent, align: right"]320000[/TD]
[TD="bgcolor: transparent, align: right"]640000[/TD]
[TD="bgcolor: transparent, align: right"]1280000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.87[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.83[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.79[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.74[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.70[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.66[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.61[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.57[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.53[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.48[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.99[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.95[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.90[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.85[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.80[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.75[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.70[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.65[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.60[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.55[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.87[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.83[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.79[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.74[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.70[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.66[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.61[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.57[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.53[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.48[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 4.75[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 4.54[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 4.30[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 4.06[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 3.82[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 3.59[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 3.35[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 3.11[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.87[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.64[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.18[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.13[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.07[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.01[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.95[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.89[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.83[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.77[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.71[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.65[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.48[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.37[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.24[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.12[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.00[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.87[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.75[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.62[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.50[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.38[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.44[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.33[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.21[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 2.09[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.96[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.84[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.72[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.60[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.48[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.35[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.87[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.83[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.79[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.74[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.70[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.66[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.61[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.57[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.53[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 0.48[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Region[/TD]
[TD="bgcolor: transparent"]Users[/TD]
[TD="bgcolor: transparent"]Monthly Charge[/TD]
[TD="bgcolor: transparent, colspan: 3"]Average Price per Product[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl2002, bgcolor: yellow, align: right"]5500[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 6,315.00[/TD]
[TD="class: xl2003, bgcolor: transparent, align: right"]€ 1.15[/TD]
[TD="class: xl2003, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]


Keeps returning an incorrect answer which an increasing average price. It should return a saving for an increase in users . For instance...

5500 users in region 1 should equal (5000*0.87)+(500*0.83)=4765, however this equation will give 6315. Im not sure if it's how i've structured the formula itself, or something to do with the size of the brackets. ie. brackets 1 and 2 are both 5000 users so the second and third queries both assume 500 users remaining

Feedback is much appreciated

C


Split from: http://www.mrexcel.com/forum/showthread.php?630798