{=AVERAGE(IF(C10:H10<>0,c10:h10)}

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Jan 31, 2006

• #2

Try:
=SUM(C10:H10)/COUNTIF(C10:H10,">0")


Michael

 

Upvote 0

A

aka_krakur

Active Member

Joined

Jan 31, 2006

Messages

438

• Jan 31, 2006

• #3

only thing that's not answering is using data from multiple worksheets.

 

Upvote 0

A

aka_krakur

Active Member

Joined

Jan 31, 2006

Messages

438

• Jan 31, 2006

• #4

=SUM((C10:H10),('Wk 1 - P'!C10:H10))/COUNTIF((C10:H10),('Wk 1 - P'!C10:H10)>"0")

I used this formula as well, and when I hit enter I get a #DIV/0! in the cell.

 

Upvote 0

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Jan 31, 2006

• #5

Try:
=SUM('Wk 1 - P'!C10:H10))/COUNTIF('Wk 1 - P'!C10:H10)>"0")

Is Your worksheet name?? "Wk 1 - P" ?


Michael

 

Upvote 0

A

aka_krakur

Active Member

Joined

Jan 31, 2006

Messages

438

• Jan 31, 2006

• #6

That's one of the worksheets. I have 4 different worksheets that I need to get a running daily average on. Each worksheet is a new week of that month. So I have Wk 1 - P, Wk 2 - P, Wk 3 - P, & Wk 4 - P (the range is the same on each worksheet (C10:H10)

 

Upvote 0

daniels012

Well-known Member

Joined

Jan 13, 2005

Messages

5,219

• Jan 31, 2006

• #7

Did you try my formula?
=SUM('Wk 1 - P'!C10:H10))/COUNTIF('Wk 1 - P'!C10:H10)>"0")

Michael

 

Upvote 0

A

aka_krakur

Active Member

Joined

Jan 31, 2006

Messages

438

• Jan 31, 2006

• #8

right, but that will only get me the average on worksheet Wk1 - P. I need to get the average on all 4 worksheets

 

Upvote 0

T

toddb

New Member

Joined

Jan 19, 2006

Messages

6

• Jan 31, 2006

• #9

Its kinda long, but it works....

=SUM(c10:h10,'Wk 1 - P'!C10:H10)/(COUNTIF('Wk 1 - P'!C10:H10,">0")+COUNTIF('Wk 1 - P'!C10:H10,"<0")+COUNTIF(c10:h10,">0")+COUNTIF(c10:h10,"<0"))

 

Upvote 0

D

Domenic

MrExcel MVP

Joined

Mar 10, 2004

Messages

21,603

Office Version

1. 365

Platform

1. Windows

• Jan 31, 2006

• #10

Let A1:A4 contain a list of your sheet names...

Wk 1 - P
Wk 2 - P
Wk 3 - P
Wk 4 - P

Then, try the following formula...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$1:$A$4&"'!C10:H10"),">0"))

Hope this helps!

 

Upvote 0