• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Median age for many columns

Dubs

New Member
Hi team, I am having difficulty working out the median age for each occupation in the list attached.
i.e. manually I know the median age for occupation 1 in column B should be around age 46 but I need a formula to use over many different columns and to get an accurate age for each.

Thanks.
 

Attachments

Peter Bartholomew

Well-Known Member
Broadly, what you have to do is take a given occupation and accumulate the count of individuals over years age. In this case the total count is 70, so the idea is to look up the age of the 35th person.
76828

Sadly, it is almost certain that I do not use the same version of Excel as you, so my solution is not usable without substantial change.
Code:
= BYCOL(individuals,
    LAMBDA(histogram,
      LET(
         accumulated,  SCAN(0, histogram, LAMBDA(a,n, n+a)),
         midValue, QUOTIENT(MAX(accumulated),2),
         median, --XLOOKUP(midValue, accumulated, age, "", 1),
      IF(midValue>0, median, "No individuals"))
   ))
If you wanted to settle for the mean rather than the median, that would be easier to calculate without needing helper ranges.
 

Attachments

p45cal

Well-Known Member
In the attached, a table at cell O1, right-click and choose Refresh to update it.
It gets its data from your table which has been made into a proper Excel Table called Table1.
You can change the headers on Table1, but not the AGE header which should remain as is, caps as well. You can also change the number of occupation columns as well as the number of rows as long as the extents of the table Table1 cover all your data.
 

Attachments

Top