Array Average - Multiple Conditions?

[Ted]

This is the average formula I am using right now:

{=AVERAGE(IF(ISNUMBER(D$2$32),(IF($B$2:$B$31=$B36,D$2$31))))}

B2:B7 are the 7 days of the week, Monday through Sunday
D232 are 31 days worth of numerical data

This formula finds the average for just the Wednesdays. It ignores blanks.
And it works great.

However I want to modify it to also ignore any 0's and any numbers greater
than 75. I can get it to do one of the two but not both.

Thanks any advance for any help.

 

[Luke M]

It was a little unclear to me, but I believe your criteria you mention were
to be applied to the D range?

Array** formula:

=AVERAGE(IF((ISNUMBER(D$2$31))*($B$2:$B$31=$B36)*($D$2:$D$31<>0)*($D$2:$D$31<=75),D$2$31))

**Use Ctrl+Shift+Enter to confirm, not just Enter

 

[Ted]

Thanks for your reply. While the formula you suggested worked in column D,
it did not work in some of the other columns and rows I attempted to paste
it into, even after removing some of the absolute cell references. I got
#DIV/0! errors.

I will attempt to describe the worksheet:

31 rows: one for each day of a 31 day month
24 columns: one for each hour of the day, starting at 7am and running
through 6am.
There are whole numbers in each of the cells ranging from 0 to 700.
This data are in rows D2:AA32

Row 1 is header
Column A, row 2-32 contains the =WEEKDAY formula - identfiies the day of the
week based on the date in column C
Column B, row 2-32 contains the =VLOOKUP formula - returns the day of the
week based on the result of column A (for example 3 in A2 returns
"Wednesday")
Column C, row 2-32 contains the date of the month. C2=July 1; C32=July 31.

Rows 34-40 contain averages. One row for each day of the week, starting
with Monday. So D34 returns the average of the 4 or 5 cells that contain
monday-7am data for the month. AA40 returns the average of the 4 or 5
Sunday 6am data for the month.

I want the average function to ignore 0's and numbers larger than 75.

I can post this spreadsheet online if necessary.

I am grateful for your help.

 

[Ted]

Here is a link to the file in case my description did not make sense. Again
I am looking for a way to average a column of numbers; excluding 0's, blanks
(or non-numbers) and any numbers larger than 75. Each column contains
numbers for each day of the month; each average will be for a specific day
of the week.

http://www.mediafire.com/file/wyztaoo5kxz/report.xls

I am grateful for any additional suggestions.

Thanks,

-Ted