Median price for a table with price ranges and inventory

Hi dears,

I have a chart with data that contains in a column price ranges and in a second column inventory for those price ranges, as in example below:

column a: 19,99 29,99 39,99
column b: 1, 4, 3

So basically for 19,99 price range I have only 1 product, for 29,99 I have 4 products, and for 39,99 I have 3 products.

I want to calculate the median price range, according to the inventory.

Is there a formula that can work? I searched all over internet, it only show the median but not according to the stock.

Thank you!

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Maybe try this:

=MEDIAN(DROP(REDUCE(0,A2:A4,LAMBDA(x,y,VSTACK(x,EXPAND(y,MAX(OFFSET(y,,1),1),,y)))),1))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Why didn't you provide this layout right at the start???

Lesson learned, then, I hope!!!

In X2 copied down:

=LET(a,VSTACK(FILTER($B$1:$U$1,B2:U2<>""),FILTER(B2:U2,B2:U2<>"")),MEDIAN(DROP(REDUCE(0,INDEX(a,1),LAMBDA(x,y,VSTACK(x,EXPAND(y,MAX(INDEX(a,2),1),,y)))),1)))

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Here is a solution with a shorter formula:

Please try in X2 and copy down:

Formula:

Please Login or Register  to view this content.

I don't think either formula (mine or Hans') is actually working correctly.

I am looking at this, but at the moment have no solution.

Forget that - I believe that Hans' formula is working. Mine is not!

My formula of Post #12 is indeed working well.

I have also corrected Ali's formula.
Please try in X2 and copy down:

Formula:

Please Login or Register  to view this content.

This one also works well.

Thanks, but there was no need, since yours is shorter.

But my formula messes with the Offset function and row numbers.
Your formula doesn't do that.

It can be educational for members to demonstrate both approaches working well.

That's true.