Average help???
 

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

Average help???
 

The OP replied in a different thread that he got it working.

John wrote:

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

--

Dave Peterson

Average help???
 

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

"Dave Peterson" wrote:

The OP replied in a different thread that he got it working.

John wrote:

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

--

Dave Peterson

Average help???
 

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


John wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200905/1

Average help???
 

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?




John wrote:

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

"Dave Peterson" wrote:

The OP replied in a different thread that he got it working.

John wrote:

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

--

Dave Peterson


--

Dave Peterson