Array Average - Multiple Conditions?

T

Ted

This is the average formula I am using right now:

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

B2:B7 are the 7 days of the week, Monday through Sunday
D2:D32 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.
 
L

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:D$31))*($B$2:$B$31=$B36)*($D$2:$D$31<>0)*($D$2:$D$31<=75),D$2:D$31))

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

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.
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top