Average Function Problem

J

John

I am using the following formula in Excel 2003

=ROUND(AVERAGE(A4:T4),1)

and I need to ignore any values that contain a * can any please help??

Thanks in advance

John
 
B

Bernie Deitrick

John,

Any displayed values that contain a * should be text, so they should be ignored.

Any cell that contains a formuls like =A1*4 will not be ignored, since they will return a value.

So, what do you mean, exactly?

HTH,
Bernie
MS Excel MVP
 
J

Jacob Skaria

Try this

=Round(AVERAGE(IF(A4:T4>0,A4:T4)),1)

If this post helps click Yes
 
J

John

Jacob,

Unfortunately I copied and pasted your response and the error message
appeared #VALUE!

Thanks,

John
 
J

Jacob Skaria

Sorry forgot to mention that this is an array fomula. Within the cell in edit
mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this
formula. If successful in the Formula Bar you can notice the curly braces at
both ends "{=<formula>}"
 
J

John

In a row I have values in cells from for example A4:T4 but on the end of some
of the value I have a * and I want the average to ignore the velues with a *

Example

55,67,89*,100,10*

Thanks
 
J

John

Jacob,

That fantastic it works really well, many thanks for the response.

I have another small issue that you may be able to help me with?

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 *

Once again thanks
 
Top