Can i get a sum function to recognise X (letter) as a number?
 

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 i get a sum function to recognise X (letter) as a number?
 

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 i get a sum function to recognise X (letter) as a number?
 

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??

Can i get a sum function to recognise X (letter) as a number?
 

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 i get a sum function to recognise X (letter) as a number?
 

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??

Can i get a sum function to recognise X (letter) as a number?
 

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??

Can i get a sum function to recognise X (letter) as a number?
 

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??

Can i get a sum function to recognise X (letter) as a number?
 

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??