Conditional Median calculation from a database

[ambracat]

I have been trying to find a way to calculate a median from a database
using and IF function. For example, I need to get the median of sales
prices, by year, by Area, from a database which has daily sales for
four years and six areas. I was hoping that a pivot table could be
used, but I have not been able to make it work for this use. I have
repeatedly tried to nest IF functions to do this without success. The
database has about 20,000 records in it.

Of course, I could sort by Area and by Date and then use the MEDIAN
function, but it seems to me that with all the power of Excel, there
should be an easier way. I am using Excel 2002.

Thanks for any help you can give me.

 

[Juan Sanchez]

I cannot belive it... I just had the exact same problem a
couple of weeks ago... it's a small world after all...

This is what I ended up with... hope it helps...

Say you have:
Year on column A1:A100
Areas on column B1:B100
Sales on column C1:C100

On Cell D1 You have the year to evaluate
On Cell E1 You have the area to evaluate


Of course data base can be alot biger but for i.e.
purposes this will work...

I used an array formula that for the data avobe would look
like this. type it on cell F1

=MEDIAN(IF((A1:A100=D1)*(B1:B100=E1)=1,C1:C100,""))

And since it is an array formula instead of just Enter at
the end, use CTRL+ALT+ENTER... you'll know if you did it
right if brackets appear enclosing the formula {Formula}

I also thought of a pivot table but could not makeit...

If anyone knows how to use a function different of those
listed on the Field Settings>Summarized by list on a Pivot
Table PLEASE LET US KNOW...


Cheers
Juan

 

[Dave R.]

And since it is an array formula instead of just Enter at
the end, use CTRL+ALT+ENTER... you'll know if you did it
right if brackets appear enclosing the formula {Formula}

Juan, I think that's CTRL SHIFT ENTER

 

[Juan Sanchez]

Yes of course... my mistake...I've allready slap my hand

Thanks
Juan

 

[Tom Ogilvy]

the function provided are the functions available.

 

[ambracat]

Thanks Juan, I'll try it!

I cannot belive it... I just had the exact same problem a
couple of weeks ago... it's a small world after all...

This is what I ended up with... hope it helps...

Say you have:
Year on column A1:A100
Areas on column B1:B100
Sales on column C1:C100

On Cell D1 You have the year to evaluate
On Cell E1 You have the area to evaluate


Of course data base can be alot biger but for i.e.
purposes this will work...

I used an array formula that for the data avobe would look
like this. type it on cell F1

=MEDIAN(IF((A1:A100=D1)*(B1:B100=E1)=1,C1:C100,""))

And since it is an array formula instead of just Enter at
the end, use CTRL+ALT+ENTER... you'll know if you did it
right if brackets appear enclosing the formula {Formula}

I also thought of a pivot table but could not makeit...

If anyone knows how to use a function different of those
listed on the Field Settings>Summarized by list on a Pivot
Table PLEASE LET US KNOW...


Cheers
Juan

 

[Juan Sanchez]

Thanks Tom, thats what I thaught....

Juan

-----Original Message-----
the function provided are the functions available.

--
Regards,
Tom Ogilvy




.