0

I have a big Dataset it is structured in such a way that always 16 rows belong to the same group. (e.g.: A1, A2, A3, A4, A5, A6, …A16 -> Group1; A17, A18, A19, A20,…,A32 -> Group2..etc.)

I would like to compare every Person of a group (e.g.: Group 1) with all the others of the same group (e.g.: Group 1) and retrieve the sum of each of the smaller number out of the two compared cells into another cell (without one’s owns comparison).

Herefore I have a formula that I came up with, which gives me the information I need & until here it works! The Formula I use in Excel is the following:

=SUM(MIN(A1,A$1),MIN(A1,A$2),MIN(A1,A$3),MIN(A1,A$4),MIN(A1,A$5),MIN(A1,A$6),MIN(A1,A$7),MIN(A1,A$8),MIN(A1,A$9),MIN(A1,A$10),MIN(A1,A$11),MIN(A1,A$12),MIN(A1,A$13),MIN(A1,A$14),MIN(A1,A$15),MIN(A1,A$16))-A1

But now the problem is that this formula works only for the first 16 rows (A1 - A16). Is there a possibility to make the formula change every 16. row?

Or how can I state the formula in such a way that when it reaches row A17 it references the now following 16 cells (A17 to A32) and not the previous 16 cells (A1 to A16)?

Doing it by hand is not an option in this case since I have thousands of data rows and the formula should switch its reference every 16. row of the dataset and refer to the 16 rows that belong to the same group.

  • Ignore this comment, asked a question then figured it out. – gtwebb Jun 2 '16 at 16:10
0

First off I simplified your original formula using an array formula (needs to be entered by pressing ctrl+shift+enter

=SUM(IF(A1<$A$1:$A$16,A1,$A$1:$A$16))-A1

Then it needs to be dynamic

This formula returns 1 for the first 16 rows of the sheet

=(INT((ROW()-1)/16)*16+1)

This formula returns 16 for the first 16 rows of the sheet

=(INT((ROW()-1)/16)+1)*16

These were then input into an index function to return cells A1 (below) and A16

INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1))

All these formulas were put together to create the final formula which still needs to be entered using ctrl+shift+enter.

It assumes your total range is A1:A32 which will need to be extended for your data set. Also if your data doesn't start in row 1 you will need to adjust the Int formulas.

=SUM(IF(A1<INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16)),A1,INDEX($A$1:$A$32,(INT((ROW()-1)/16)*16+1)):INDEX($A$1:$A$32,((INT((ROW()-1)/16)+1)*16))))-A1

Here is a picture of the first array formula working. The rest of the answer just makes it so it changes every 16 cells.

enter image description here

  • Something in your Formula does not really work for what I want to use it. I think I got missunderstood in a certain point which is the following: I would like to compare every Person of a group with all the others OF THE SAME GROUP! and retrieve the sum of each smallest No. of the compared cells. The Formula I use in Excel for Group 1 (e.g. Row 1 to 16) is the following: =SUM(MIN(A1,A$1),MIN(A1,A$2),MIN(A1,A$3),MIN(A1,A$4),MIN(A1,A$5),MIN(A1,A$6),MIN(A1,A$7),MIN(A1,A$8),MIN(A1,A$9),MIN(A1,A$10),MIN(A1,A$11),MIN(A1,A$12),MIN(A1,A$13),MIN(A1,A$14),MIN(A1,A$15),MIN(A1,A$16))-A1 – user600794 Jun 3 '16 at 8:25
  • PROBLEM: formula works only for rows A1 to A16 For Group 2, Row 17 to 33. I use following Formula if I do it by hand: =SUM(MIN(F18,F$18),MIN(F18,F$19),MIN(F18,F$20),MIN(F18,F$21),MIN(F18,F$22),MIN(F18,F$23),MIN(F18,F$24),MIN(F18,F$25),MIN(F18,F$26),MIN(F18,F$27),MIN(F18,F$28),MIN(F18,F$29),MIN(F18,F$30),MIN(F18,F$31),MIN(F18,F$32),MIN(F18,F$33))-F18 How can I make the Formula change like I did for Row 1to16 into Row 17to33 by itself for every next 16 rows? – user600794 Jun 3 '16 at 8:25
  • Already the array formula =SUM(IF(A1<$A$1:$A$16,A1,$A$1:$A$16))-A1 that you suggested somehow does not give me the right numbers out! Because it does not refer to the same things that my formula did before.. – user600794 Jun 3 '16 at 8:27
  • It refers to the exact same thing as your formula did when it was put in B1. Were you pressing ctrl+shift+enter. Excel should add curly brackets around your formula when you do this. And the formula I gave you changes every 16 rows. – gtwebb Jun 3 '16 at 16:11
  • Added a picture to show the original array formula working. – gtwebb Jun 3 '16 at 16:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.