Forum Discussion
Median with 2 criteria
=MEDIAN(IF((Range1=criteria 1)*( Range2 =criteria 2),Range to calculate if true))
You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
In my understanding there should be Range1 and Range2 for criteria 1 and criteria2 unless you want eiter criteria1 or criteria2 for the same range. Then you can try this formula:
=MEDIAN(IF((Range=criteria 1)+( Range =criteria 2),Range to calculate if true))
Ive tried this however, I received a #Name? issue. I've tried to copy and paste the figures and it was fine.. therefore I'm assuming the format of the cell itself is correct so I'm not sure why theres a name issue. For all the range I have included the name of the Columns and for the criteria i have tried typing with "" and also referencing to a cell but it still doesnt work
Does it work when you enter the sample data from the screenshot in your worksheet? Otherwise can you attach a screenhot of your worksheet without sensitive data which shows all the criteria, range and the formula you've applied?
- Unfortunately still doesnt work. I have tried to alter the data and i realise the formula is just not recognising the numbers. if i enter new numbers e.g. the ones you have screenshot the formula works. however if i use my data it doesnt work. however i have tried general and number formatting.
Perhaps your data are text values that look like numbers.
Try the following:
Select a column with values.
On the Data tab of the ribbon, click Text to Columns.
Click Finish.
Does that make a difference?
