Advertisements
I’ve got a column of ages and a column of the count of those ages. I need these to be transformed into a set that I can run statistical functions like average on. For example:
| Age | Count |
|---|---|
| 14 | 2 |
| 16 | 1 |
| 17 | 3 |
This needs to become (14,14,16,17,17,17) so that I could use =average or =stddev more easily on it. I have figured out a workaround to calculate average based off the formula but am stuck on standard deviation. There is probably a simple solution but I’m unable to find it on google.
>Solution :
Try using the following formula:
• Formula used in cell D2
=XLOOKUP(SEQUENCE(SUM(B2:B4)),SCAN(0,B2:B4,LAMBDA(x,y,x+y)),A2:A4,,1)
Or,
• Formula used in cell D2
=TEXTSPLIT(TRIM(CONCAT(REPT(A2:A4&" ",B2:B4))),," ")/1