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"