Search code examples
excelexcel-formula

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

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:

    enter image description here

    • Formula used in cell D2

    =XLOOKUP(SEQUENCE(SUM(B2:B4)),SCAN(0,B2:B4,LAMBDA(x,y,x+y)),A2:A4,,1)

    Or,

    enter image description here

    • 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:

    enter image description here

    =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:

    enter image description here

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