I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

What number do you want your x to be recognised as?

The SUM() and AVERAGE() functions will ignore a cell containing text, so
your sum and average would look at the numbers in your range, and include
that cell when you change it from X to a number.

If you want x to be treated as some specific number, tell us what number you
want it treated as.
--
David Biddulph

"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

I think you want this array formula

=AVERAGE(IF(ISNUMBER(I1:I5),I1:I5))

As an arary formula, commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

Yes, i need it to be recognised as 5.

"David Biddulph" wrote:

What number do you want your x to be recognised as?

The SUM() and AVERAGE() functions will ignore a cell containing text, so
your sum and average would look at the numbers in your range, and include
that cell when you change it from X to a number.

If you want x to be treated as some specific number, tell us what number you
want it treated as.
--
David Biddulph

"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

can you elaborate al little? which cell (s) does this go in?

"Bob Phillips" wrote:

I think you want this array formula

=AVERAGE(IF(ISNUMBER(I1:I5),I1:I5))

As an arary formula, commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

I dont think this is what im looking for

"Bob Phillips" wrote:

I think you want this array formula

=AVERAGE(IF(ISNUMBER(I1:I5),I1:I5))

As an arary formula, commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob

"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

Hi Nomad,

Maybe this might work,
=SUM(A1:A30)+(COUNTIF(A1:A30,"x")*5)

You will rpobably need to add some sort of error checking
but that depends on how your sheet is set-up.

HTH
Martin


"nomad" wrote in message
...
I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed
in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??

Say we have a column of mixed values:

13
13
13
13
13
happy
13
13
13
sad
13
13
13
13
13
13
13

We want to sum the column, but want ANY text cell to be treated as a 5.

=SUM(A1:A17)+5*(COUNTA(A1:A17)-COUNT(A1:A17))

will display 205
--
Gary''s Student - gsnu200800


"nomad" wrote:

I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??