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;
<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>
</TBODY>
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
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;
| Users | ||||||||||
| Region | 0 | 5000 | 10000 | 20000 | 40000 | 80000 | 160000 | 320000 | 640000 | 1280000 |
| 1 | € 0.87 | € 0.83 | € 0.79 | € 0.74 | € 0.70 | € 0.66 | € 0.61 | € 0.57 | € 0.53 | € 0.48 |
| 2 | € 0.99 | € 0.95 | € 0.90 | € 0.85 | € 0.80 | € 0.75 | € 0.70 | € 0.65 | € 0.60 | € 0.55 |
| 3 | € 0.87 | € 0.83 | € 0.79 | € 0.74 | € 0.70 | € 0.66 | € 0.61 | € 0.57 | € 0.53 | € 0.48 |
| 4 | € 4.75 | € 4.54 | € 4.30 | € 4.06 | € 3.82 | € 3.59 | € 3.35 | € 3.11 | € 2.87 | € 2.64 |
| 5 | € 1.18 | € 1.13 | € 1.07 | € 1.01 | € 0.95 | € 0.89 | € 0.83 | € 0.77 | € 0.71 | € 0.65 |
| 6 | € 2.48 | € 2.37 | € 2.24 | € 2.12 | € 2.00 | € 1.87 | € 1.75 | € 1.62 | € 1.50 | € 1.38 |
| 7 | € 2.44 | € 2.33 | € 2.21 | € 2.09 | € 1.96 | € 1.84 | € 1.72 | € 1.60 | € 1.48 | € 1.35 |
| 8 | € 0.87 | € 0.83 | € 0.79 | € 0.74 | € 0.70 | € 0.66 | € 0.61 | € 0.57 | € 0.53 | € 0.48 |
| Region | Users | Monthly Charge | Average Price per Product | |||||||
| 1 | 5500 | € 6,315.00 | € 1.15 |
<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>
</TBODY>
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
Last edited by a moderator:
