[b]My averages are incorrect - help please![/b]

miltinstlouis

New Member
Joined
Nov 22, 2005
Messages
2
When I average a column of numbers the averages are incorrect. I know it has something to do with how Excel stores the numbers in memory - but I'm not sure what the fix is - maybe the round function. All help is GREATLY appreciated!!
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
If U R averaging ranges including zeroes, that is the problem. There are formulas you can write to discount the zero values.

examples:
=SUM(
)/COUNTIF(
,">0") =SUM(
)/(COUNT(
)-COUNTIF(
,0)) where
is the range you are averaging. Caution: There are times where zero will be a valid entry and needs to be included in the average.
 

miltinstlouis

New Member
Joined
Nov 22, 2005
Messages
2
I am simply summing a column of numbers that are dollars and cents and then using the average function. When I add up the columns with a calculator I do not get the same totals.

Is there a simple fix?
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
Other than the zero issue, I don't know of a problem with Excel's average function.

Oh wait! First you asked about averaging and now you're asking about totaling and summing?

It is probably the case that excel is rounding up "hidden" decimals.

to eliminate that problem, use the Round() function or any of its relatives. (See Excel Help)

Here some other info:
http://www.mrexcel.com/archive2/6300/6975.htm

http://www.mrexcel.com/archive2/18200/20674.htm

http://www.xldynamic.com/source/xld.Rounding.html
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,119
miltinstlouis said:
I am simply summing a column of numbers that are dollars and cents and then using the average function. When I add up the columns with a calculator I do not get the same totals.

Is there a simple fix?
What are the results these two engines yield?
 

Forum statistics

Threads
1,078,253
Messages
5,339,111
Members
399,279
Latest member
danidanidaniel

Some videos you may like

This Week's Hot Topics

Top