Median formula showing highest value from range.

Abhiraj Joshi

Hi everyone,

I have two sheets, a master sheet with cost data and a metric sheet with formulas to get a summary.

In metric sheet, I need median value of certain items. I have used MEDIAN & COLLECT.

Problem:

When I updated the formula, I got the correct median value. But after sometime, the metric sheet gets refreshed automatically and the cell with median formula shows the highest value from that range.

e.g:

Product 1 5000

Product 2 8000

Product 3 3000

The median should be 5000. But the metric sheet gets refreshed and I get the value as 8000.

Is there something wrong with the way I am using the formula or the way data is set up?

Paul Newcome

Try this...

Remove all instances of the formula.

Log out.

Clear cookies and cache.

Log back in.

Replace formula.

Bassam Khalil

Hi @Abhiraj Joshi

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Abhiraj Joshi

Above is the database. As a sample I have marked the data in red for which I have calculated median.

This is the result I am getting. When I type the formula, the out put is correct. But the sheet keeps refreshing and showing the highest value of the data.

Paul Newcome

Exactly what is being covered by the Temp1 Range 1} range? Did you specifically select ONLY those cells highlighted in red in your screenshot, or do you have other cells/entire column included in the range?

Abhiraj Joshi

Temp1 Range1 in this example, I have selected only cells highlighted.

In my original sheet, I have used MEDIAN & COLLECT formulas together and selected the entire column.

Paul Newcome

Try this...

Remove all instances of the formula.

Log out.

Clear cookies and cache.

Log back in.

Replace formula.

Abhiraj Joshi

Sure. Will try it. Thanks.