Super User Asked on December 11, 2021
Say there are 3 columns: The first column indicates names, the second indicates some string variables including height and weight in random order and the third indicates corresponding numeric values.
I am interested in the weight/height ratio of each person. Is there any smarter way to compute all the ratios instead of calculating them one by one? Please see the image below.
A B C
Mike height 170
Mike weight 40
Mike income 1000
Mike sex 0
Anna income 1500
Anna weight 60
Anna height 170
Carl sex 1
Carl weight 60
Carl height 150
Jo height 190
Jo weight 47
Jo sex 1
From the 3 columns above, I want to compute the ratio of weight to height for each person as follows:
D E
Mike 0.235294118
Anna 0.352941176
Carl 0.4
Jo 0.247368421
If you have Excel O365, this can be done with a single formula in a single cell due to Excel's spilling behaviour. It's purely for educational purposes since more simple formulas are possible. What I tried in E1
:
=TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height")))
If you then want to SORT
by person's name (or weight/height ratio) you can do so by:
Name:
=SORT(TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height"))),1,1)
Ratio:
=SORT(TRANSPOSE(CHOOSE({1,2},TRANSPOSE(UNIQUE(A1:A13)),SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"weight")/SUMIFS(C1:C13,A1:A13,TRANSPOSE(UNIQUE(A1:A13)),B1:B13,"height"))),2,1)
Answered by JvdV on December 11, 2021
In T1 you can use the below formula, then drag down for the other names.
=SUMIFS(Q:Q,O:O,S1,P:P,"HEIGHT")/SUMIFS(Q:Q,O:O,S1,P:P,"WEIGHT")
Ahh this answer was to go with the original image.
You may need to adjust the columns to fit if you have moved the columns as per the update to the question.
Answered by PeterH on December 11, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP