Median question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

_Scott_ - 13 Jul 2006 06:05 GMT

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

Reply to this Message

Franz Verga - 13 Jul 2006 13:15 GMT

> Hi Guys,
>
[quoted text clipped - 14 lines]
> 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

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Domenic - 13 Jul 2006 14:22 GMT

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!

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

> Hi Guys,
>
[quoted text clipped - 15 lines]
> salesprices not the ones matching my criteria.any help will be greatly
> appreciatedcheersScott

Reply to this Message