I have a spreadsheet and I need to have formulas that will check column A
for a specific customer and if it is the specific customer, I need it to look
at column F and give me the average, median, total, min, and max items
ordered. I am trying to use array formulas and the max and total formulas
work, but the other three do not. Could you tell me what I may be doing
wrong? Here are the two array formulas that work.
=MAX((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
=SUM((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
Any help you can give me would be appreciated.
Betsy"

If I read your post correctly, I think the others may not 'working' because
they are not ignoring zeros (it may be that you want to include zeros if so -
disregard this response)

Here is what I would do for the MEDIAN formula (the others should be similar).

{ =MEDIAN( IF(((DataEntry!$A$4:$A$10=$A5)* (DataEntry!$F$4:$F$10))<>0,
((DataEntry!$A$4:$A$10=$A5)* (DataEntry!$F$4:$F$10))) ) }

Goodluck
T

"Liz" wrote:

> I have a spreadsheet and I need to have formulas that will check column A
> for a specific customer and if it is the specific customer, I need it to look
> at column F and give me the average, median, total, min, and max items
> ordered. I am trying to use array formulas and the max and total formulas
> work, but the other three do not. Could you tell me what I may be doing
> wrong? Here are the two array formulas that work.
> =MAX((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
> =SUM((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
> Any help you can give me would be appreciated.
> Betsy"
>

Thank you so much for your help. Everything works great.

Liz

"Serena595" wrote:

>
> If I read your post correctly, I think the others may not 'working' because
> they are not ignoring zeros (it may be that you want to include zeros if so -
> disregard this response)
>
> Here is what I would do for the MEDIAN formula (the others should be similar).
>
> { =MEDIAN( IF(((DataEntry!$A$4:$A$10=$A5)* (DataEntry!$F$4:$F$10))<>0,
> ((DataEntry!$A$4:$A$10=$A5)* (DataEntry!$F$4:$F$10))) ) }
>
> Goodluck
> T
>
> "Liz" wrote:
>
> > I have a spreadsheet and I need to have formulas that will check column A
> > for a specific customer and if it is the specific customer, I need it to look
> > at column F and give me the average, median, total, min, and max items
> > ordered. I am trying to use array formulas and the max and total formulas
> > work, but the other three do not. Could you tell me what I may be doing
> > wrong? Here are the two array formulas that work.
> > =MAX((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
> > =SUM((DataEntry!$A$4:$A$2000=$A5)*DataEntry!$F$4:$F$2000)
> > Any help you can give me would be appreciated.
> > Betsy"
> >