How to convert a column of age counts into a set useful for statistics?

Question

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.

Answer 1

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

---

Edit: 3/28/2024

• Using TOCOL() & SEQUENCE() functions, bit shorter way:

---

=TOCOL(A1:A4/(SEQUENCE(,MAX(B1:B4))<=B1:B4),2)

---

Note: The above won't work with Text values, for that a bit change refer below:

---

=TOCOL(IFS(SEQUENCE(,MAX(B1:B4))<=B1:B4,A1:A4),2)

---