Conditoinal median calculation for a series

**mahershams** · 11-24-2012, 09:18 AM

Hi I would like to calculate median value for year 2000-2011 in cell B5808. I tried a formual but i do not why it gives no value or wrong value. I give you the manual answer in b5822 cell. Please help me.

Thanks for continuous help.

**mahershams** · 11-24-2012, 09:34 AM

This median calculation is based on industry type A column. For example median values for basic materials in year 2000 (manual answer given in B5822 CELL). I have also given my formula in B5808 which did not work.. Since there are some blank cells, it may impact the calculation.

**Palmetto** · 11-24-2012, 09:35 AM

{=MEDIAN((IF($A$2:$A$5797="Basic Materials",IF($D$2:$D$5797<>"",$D$2:$D$5797))))}

Array formula are entered using the key combination of Alt + Shift + Enter
Excel will automatically add the { } around the formula

**mahershams** · 11-24-2012, 09:43 AM

I do not why it does not work. It gives me #VALUE!. Can you please kindly provide me the excel sheet with the formula please.

**Palmetto** · 11-24-2012, 10:03 AM

When correctly entered, the formula works. It gave me the same result you showed for the manual calculation.

You must make absolutely sure to two things when entering array formula:
1. *ALL* ranges must be exactly the same size (you had an error in your workbook - mistmatched range sizes)
2. You must enter the array formula using Alt+Shift+Enter