Excel:Averaging cells with Numbers and Blanks 2

[BYarn]

We have a spreadsheet with various data, totals and averages. A typical row might look like:

JAN FEB MAR APR MAY JUNE Average
144 157 151 153 1064

New data is entered for each month. In this example we started in Feb and haven't entered June yet. The formula under Average is meant to not change every month. So if we enter June, the formula will average by 5 and not 4 months.

The problem-we want to average the row by totaling the numbers (144....153) and then divide by the months with ACTUAL data. The formual needs to determine what does and doesn't have data and divide accordingly. The cells may be blank or they may have a ZERO which is a formula totaling "0" WHAT formula goes under "Average

 

[macropod]

Try something like:
=SUM(Range)/COUNT(Range)
This will give you the average of all months that have values (including 0) attributed to them, and works on the premise that the months not to be counted do not have numbers in them.

Cheers

 

[mbarron]

BYarn,
=Average(Range) will work

macropod,
Your formula defines the Average() function

Examples

If A1:A5 is named Scores and contains the numbers 10, 7, 9, 27, and 2, then:

AVERAGE(A1:A5) equals 11

AVERAGE(Scores) equals 11

AVERAGE(A1:A5, 5) equals 10

AVERAGE(A1:A5) equals SUM(A1:A5)/COUNT(A1:A5) equals 11

If C1:C3 is named OtherScores and contains the numbers 4, 18, and 7, then:

AVERAGE(Scores, OtherScores) equals 10.5

Mike

 

[mbarron]

If you have columns that have zero in them and don't want those columns to be included in the average you could use

=SUM(Your_Range)/COUNTIF(Your_Range,"<>0")
or
This one entered as an Array formula (crtl + shift + enter to confirm entery)
=AVERAGE(IF(Your_Range<>0,Your_Range))


Mike

 

[BYarn]

Thanks for the responces. Apparently I wasn't clear. I have already used the formulas you provided. In my SS they don't work if there is a ZERO in a cell (for example-if I were taking an average of a totals row and some of the cells in the row were "0")For example:

JAN FEB MAR APR MAY JUNE JULY Aver
2 4 6 8 10 6

The average is 6. If, however there were a "0" (zero) in Jan or July, the average would change. Since I started in Feb and haven't gotten to July in the SS, I DON'T want to average those cells in.

?? Thanks

 

[mbarron]

BYarn,
After re-readiong your original post, I gathered that was what you might have been looking for. Check my second post

Mike

 

[BYarn]

Thanks everyone- the Array formula works!! (the other-=SUM(Your_Range)/COUNTIF(Your_Range,"<>0")-doesn't

I've never used an ARRAY and I am not strong enough on "IF" formulas!

Have a good Thanksgiving

 

[BYarn]

I have a followup to this question, which is also applicable in other situations.
While using this (Array) formula =AVERAGE(IF Your_Range<>0,Your_Range)) if a row is all zero's, the formula returns a #DIV/0! How can I get that (here or in other situations) from appearing. I would rather a zero appear

so:
JAN FEB MAR APR MAY JUNE JULY Aver
0 0 0 0 0 #DIV/0!

Under AVER I want a 0


?? Thanks

 

[BYarn]

In my previous post about a row of zeros returning a #DIV/0! , that should also read, a row of blank cells (returns a #DIV/0! )

 

[KenWright]

One way:-

=IF(ISERROR(Your_Formula),0,Your_Formula)

or if negative numbers are not possible, then

=IF(SUM(Your_Range)=0,0,Your_Formula)

Still need to array enter if Your_Formula was an array formula.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[BYarn]

The last formula works fine as long as the ENTIRE row is a "0" or is blank. Since this is a spreadsheeet being prepared to accept numbers OR zeros as needed, It will be ok with that formula until someone enters a number into ANY cell in the row. The totals formula then returns a #Value!. That's, unfortunately just as bad as the original error returned. Ideas???

 

[KenWright]

Then just go with the first, or does that not do what you wnat for some reason?

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[BYarn]

I'm going with the first as suggested. It seems to do the job. Thanks! Someday I will better master "IF" statements and the multitude of parenthesis' for these Looongg formulas

Thanks again

 

[KenWright]

LOL - It's just a piece of logic, nothing more.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[BYarn]

One more "IF" function, please, on a similar but different situation:
A B .... H I
Jan Feb........ Aug Min
5 330 348 355 327 323 320 302 0 0

If I use the MIN function " =MIN(A5:H5)" to select the lowest number in the row, it returns "0" since there are no #'s in the column for that month yet or if the row is empty, etc

I want the function to read the whole row and return an "actual" # In this case 302 (rather than 0)

This should be the last question and hopefully give me the help up to figure more out

??? Thanks!!!!!!!!!!!!!!!!

 

[AnotherHiggins]

BYarn,

Please start a new thread since this question is not related to your earlier question. This makes it easier for others with the same problem to search for answers.

Also, it is traditional around here to award a STAR to someone who answered your question. This serves two purposes: It directly thanks the person who posted an answer which worked successfully; and it helps folks who might be looking for an answer to the same problem in the future know the thread has been answered.

NOTE: Please do not give me a star for this post.

[tt]-John[/tt]
The Member Profiles are finally working again!
________________________
To get the best answers fast, please read faq181-2886

 

[mbarron]

This one should do the trick.

=LARGE(G9:K14,COUNTIF(G9:K14,">0"))

Mike

 

[BYarn]

Thanks Mike. That works except for when each cell in the row is a "0" in which case it returns a #NUM! I know the is a error handling routne, but I can'r seem to get it right!

 

[mbarron]

Add an IF() to check to see if the maximum value is 0

=if(MAX(range)=0,"",LARGE(range,COUNTIF(range,">0")) )



Mike

 

[BYarn]

Ok. It worked!! Thanks to all for the help. These formula make the difference between a less accurate, through and presentable spreadsheet and just a bunch of sometimes inapproprite data! Great lessons on the formulas (hoping that I can get more figured out on my own now!)