Clustering and building Sums

excelactuary

New Member
Joined
Feb 18, 2013
Messages
45
Hi,

I have the following table:

ABCDEF
1372200927,07350
131720130250
131520090600
131520090500
137920121.177,42500
1320201018.012,33460
1374200945,821.000,00
1389201417.926,001.000,00
135420080400
13612011405,38500
23892010819,78800
239920090250
241520080500
241820110290
242020160288
248820110422
241220080533
241220161.741,94400
24672014421,94400
24692013438,71400
244820101.063,14300

<tbody>
</tbody>

In the column A and B we have only two possbile values:

A: 1 and 2
B: 3 and 4

Now, in this example D4, D5 and D14 have the number 15. But, only D4 and D5 have the same corresponding entries in A, B and D (A4=A5=1, B4=B5=3, D4=D5=2009). So, for these values I would want to calculate in a new cell the following: (E4+E5)/(F4+F5).

More generally, I would like a formula which parses the column C for identical entries (for instance: looking in the whole column for 15) and then extracting all those entries which have the same corresponding values in the column A, B, and C, and in the end calculating sum(E_i)/sum(F_i) for all i entries which were extracted in the previous step.

Do you have an idea, how to implement this?

Thank you very much!
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
I'm a little confused about what constitutes a group. I think you might have had some typos which didn't help either. However, if I consider each unique set of the values in A:D, then I came up with this:

ABCDEFGHIJKLMNO
11372200927.07350WorkABCDCountSum(E )/Sum(F )
213172013025012.242012412200810
313152009060012.242022412201614.35485
413152009050015.132011315200920
5137920121177.4250015.242012415200810
61320201018012.3346017.132011317201310
71374200945.821.000,0018.242012418201110
813892014179261.000,0020.1320113202010139.15724
913542008040020.242022420201610
1013612011405.3850048.242012448201013.5438
1123892010819.7880054.132011354200810
1223992009025061.132011361201110.81076
1324152008050067.242012467201411.05485
1424182011029069.242012469201311.096775
1524202016028872.132011372200910.077343
1624882011042274.13201137420091#DIV/0!
1724122008053379.132011379201212.35484
18241220161741.9440088.242012488201110
1924672014421.9440089.13201138920141#DIV/0!
2024692013438.7140089.232012389201011.024725
21244820101063.1430099.232012399200910

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
I2=IF($H$2="","",INT(MOD(H2,1)*10))
J2=IF($H2="","",INT(MOD($H2*10,1)*10))
K2=IF($H2="","",INT($H2))
L2=IF($H2="","",ROUND(MOD($H2*100,1)*10000,0))
N2=IF($H2="","",COUNTIFS(A:A,I2,B:B,J2,C:C,K2,D:D,L2))
O2=IF($H2="","",SUMIFS(E:E,A:A,I2,B:B,J2,C:C,K2,D:D,L2)/SUMIFS(F:F,A:A,I2,B:B,J2,C:C,K2,D:D,L2))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
H2{=MIN($C1:$C21+$A1:$A21/10+$B1:$B21/100+$D1:$D21/1000000)}
H3{=IF(H2="","",IFERROR(SMALL($C$1:$C$21+$A$1:$A$21/10+$B$1:$B$21/100+$D$1:$D$21/1000000,SUM(IF($C$1:$C$21+$A$1:$A$21/10+$B$1:$B$21/100+$D$1:$D$21/1000000<=H2,1))+1),""))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



Set up the table as shown. Put the formula in H2, and confirm with Control+Shift+Enter. Put the formula in H3, confirm with Control+Shift+Enter, then copy it down the column as far as needed. Then put in the regular formulas in I2:O2, and copy down the columns. These formulas do require that the values in A:D are numeric. The combinations are shown in A:L, sorted by C. The count in N shows that there's only one combination in this set with more than one entry (1,2,15,2009).

Let me know if I'm on the right track.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top