Median question

[_Scott_]

Hi Guys,

I am working on a median formula as follows
=MEDIAN(IF(OR(Data!$D$2:$D$29998=MCalcs!$A$11,Data!$D$2:$D$29998=$A$12),(IF(Data!$F$2:$F$29998>=MCalcs!B$7,(IF(Data!$F$2:$F$29998<=MCalcs!B$8,Data!$E$2:$E$29998))))))I have 10 product codes with monthly sales data, The worksheet Data whichstores my sales data, MCalcs is my median calulations worksheet. Thespreadsheet is pretty big storing 3 years worth of sales data, I willprobably go up to 5 years in total.What I am trying to do is work out a median of a group of numbers based onif it matches 2 product codes using this part of the formula"(IF(OR(Data!$D$2:$D$29998=MCalcs!$A$11,Data!$D$2:$D$29998=MCalcs!$A$12)"I set a date range"(IF(Data!$F$2:$F$29998>=MCalcs!B$7,(IF(Data!$F$2:$F$29998<=MCalcs!B$8"basically greater than the 1st of the month and less than the end of themonth.The "Data!$E$2:$E$29998" is the sales prices that I want the median of.My problem is my formula seems to calculate the median of the entire salesprices not the ones matching my criteria.any help will be greatly appreciatedcheersScott

 

[Franz Verga]

Hi Guys,

I am working on a median formula as follows
=MEDIAN(IF(OR(Data!$D$2:$D$29998=MCalcs!$A$11,Data!$D$2:$D$29998=$A$12),(IF(Data!$F$2:$F$29998>=MCalcs!B$7,(IF(Data!$F$2:$F$29998<=MCalcs!B$8,Data!$E$2:$E$29998))))))I
have 10 product codes with monthly sales data, The worksheet Data
whichstores my sales data, MCalcs is my median calulations worksheet.
Thespreadsheet is pretty big storing 3 years worth of sales data, I
willprobably go up to 5 years in total.What I am trying to do is work
out a median of a group of numbers based onif it matches 2 product
codes using this part of the
formula"(IF(OR(Data!$D$2:$D$29998=MCalcs!$A$11,Data!$D$2:$D$29998=MCalcs!$A$12)"I
set a date
range"(IF(Data!$F$2:$F$29998>=MCalcs!B$7,(IF(Data!$F$2:$F$29998<=MCalcs!B$8"basically
greater than the 1st of the month and less than the end of
themonth.The "Data!$E$2:$E$29998" is the sales prices that I want the
median of.My problem is my formula seems to calculate the median of
the entire salesprices not the ones matching my criteria.any help
will be greatly appreciatedcheersScott

Hi Scott,

I'm not sure, but I think you could try to use a SUMPRODUCT function,
instead of nested IFs.

If you need more help, maybe you could upload a small example file to
www.savefile.com


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Domenic]

Try...

=MEDIAN(IF((Data!$D$2:$D$29998=MCalcs!$A$11)+(Data!$D$2:$D$29998=$A$12),I
F(Data!$F$2:$F$29998>=MCalcs!B$7,IF(Data!$F$2:$F$29998<=MCalcs!B$8,Data!$
E$2:$E$29998))))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

<44b5d506$0$17546$61c65585@un-2park-reader-01.sydney.pipenetworks.com.au