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

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


"John" wrote in message
...
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

Try this

=Round(AVERAGE(IF(A4:T40,A4:T4)),1)

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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

Jacob,

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

Thanks,

John

"Jacob Skaria" wrote:

Try this

=Round(AVERAGE(IF(A4:T40,A4:T4)),1)

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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

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}"
--
If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

Jacob,

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

Thanks,

John

"Jacob Skaria" wrote:

Try this

=Round(AVERAGE(IF(A4:T40,A4:T4)),1)

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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

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

"Bernie Deitrick" wrote:

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


"John" wrote in message
...
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

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

"Jacob Skaria" wrote:

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}"
--
If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

Jacob,

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

Thanks,

John

"Jacob Skaria" wrote:

Try this

=Round(AVERAGE(IF(A4:T40,A4:T4)),1)

If this post helps click Yes
---------------
Jacob Skaria


"John" wrote:

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