Average help???

J

John

Is there anyway I can include the value of the cell with a * but not perform
the average for that sepcific cell:

for example

55,67,89*,22,37

the totals would be 270 but the average would be /4 and not /5 because of
the *

Thanks for any help

John
 
J

John

Unfortunately this is a slightly different problem and I still cannot get it
to work.
 
E

edvwvw via OfficeKB.com

In B1 you could use - drag down

=IF(ISNUMBER(A1),0,VALUE(MID(A1,1,(LEN(A1)-1))))

This checks to see if the value in A1 is a number if it is then 0, if it is a
XX*, it checks the length and returns a value.

To count the number of cells with a value, the divisor:

=COUNTIF(A1:A5,">0")

To include the value of the figure with *

=SUM(A1:A5,B1:B5)

This will produce the value that you are looking for. The XXX* can be of any
length and can be in any row.

edvwvw
 
D

Dave Peterson

If you enter
89*
in a cell, then excel will treat it as text.

And it won't be included in the average (or sum or count of cells with numbers).

So are you typing that asterisk or is that the result of formatting? Does it
represent a negative number?
 
Top