Averages

H

Help

Help. I've calculated the average of three different cells in three different
rows that are not in sequential rows. The problem is that some of the cells
may contain a zero so the formula should only divide by two instead of three.
Anyone of the cells could be zero so you may have to divide by one, two or
three. Is there a formula???
 
Y

YESHWANT

TRY ANY ONE OF THE FOLLOWING :
1. =AVERAGE(IF(A1:A7<>0, A1:A7))
This is an array formula and as such it must be commited with
Shift+Ctrl+<Enter>

2. =SUMIF(A1:A7, "<>0")/COUNTIF(A1:A7, "<>0")

click yes below, if it works
 
H

Help

thanks for the help....I should have been a little clearer
Qtr 1 Qtr 2 Qtr3
A B C D E F G H I Avg
1 3 5 3
6 5 0 5.5 (not 3.6)
0 0 8 8

Columns B, C, E, etc have numbers as well. I'm tring to get an average of
the first quarter over a six year period.
 
Y

YESHWANT

IF YOUR DATA CONTAINS DATA FOR THE FIRST QUARTER IN THE RANGE B1 TO C6, FOR
WHICH U WANT TO CALCULATE AVERAGE,
1. TYPE IN DESIRED CELL
=AVERAGE(IF(B1:C6<>0, B1:C6))
This is an array formula and as such it must be commited with
Shift+Ctrl+<Enter>

2. =SUMIF(B1:C6, "<>0")/COUNTIF(B1:C6, "<>0")

ELSE CHANGE THE RANGE SUITABLY

click yes below, if it works
 

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