Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Array Average - Multiple Conditions?

This is the average formula I am using right now:

{=AVERAGE(IF(ISNUMBER(D$2:D$32),(IF($B$2:$B$31=$B3 6,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.


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,722
Default Array Average - Multiple Conditions?

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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ted" wrote:

This is the average formula I am using right now:

{=AVERAGE(IF(ISNUMBER(D$2:D$32),(IF($B$2:$B$31=$B3 6,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.



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Array Average - Multiple Conditions?

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


"Ted" wrote in message
...
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.

"Luke M" wrote in message
...
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


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Ted" wrote:

This is the average formula I am using right now:

{=AVERAGE(IF(ISNUMBER(D$2:D$32),(IF($B$2:$B$31=$B3 6,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.







Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Average If with multiple conditions WildWill Excel Discussion (Misc queries) 1 February 10th 09 11:45 AM
AVERAGE IF with multiple conditions WildWill Excel Discussion (Misc queries) 6 February 10th 09 08:52 AM
Average with multiple conditions Dez Excel Worksheet Functions 4 July 11th 07 06:07 PM
Array Formula With Multiple Conditions JR573PUTT Excel Discussion (Misc queries) 3 February 17th 06 12:37 AM
Multiple Conditions Setup with an Array?? Not Working?? TEAM Excel Worksheet Functions 2 August 5th 05 08:40 PM


All times are GMT +1. The time now is 08:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"