Excel calculation
 

Hi all,

Newbie question. I want to calculate an overall score
where sometimes I have a NA field (not available score.
In the example below, I would like to calculate SUM Row 1
to Row 7 and only divide by the number of rows with actual
numbers...in this case it would be (13 / 4). Please help.

Thanks.

Martin

Row Score
1 2
2 3
3 NA
4 5
5 3
6 NA
7 NA

Excel calculation
 

Hi
use
=AVERAGE(A1:A7)

Average will ignore text fields

--
Regards
Frank Kabel
Frankfurt, Germany

"Martin" schrieb im Newsbeitrag
...
Hi all,

Newbie question. I want to calculate an overall score
where sometimes I have a NA field (not available score.
In the example below, I would like to calculate SUM Row 1
to Row 7 and only divide by the number of rows with actual
numbers...in this case it would be (13 / 4). Please help.

Thanks.

Martin

Row Score
1 2
2 3
3 NA
4 5
5 3
6 NA
7 NA

Excel calculation
 

One way:

If your data is in column A, then in column B enter this formula:

=IF(ISNA(A1),0,A1)

and in column C:

=IF(ISNA(A1),0,1)

extend both to the end of the data in column A (let's say A7, like in your
example)

Your formula (for 7 entries):

=SUM(B1:B7)/SUM(C1:C7)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Martin" wrote in message
...
Hi all,

Newbie question. I want to calculate an overall score
where sometimes I have a NA field (not available score.
In the example below, I would like to calculate SUM Row 1
to Row 7 and only divide by the number of rows with actual
numbers...in this case it would be (13 / 4). Please help.

Thanks.

Martin

Row Score
1 2
2 3
3 NA
4 5
5 3
6 NA
7 NA

Excel calculation
 

Martin,,
If it is just text NA, then

=AVERAGE(A1:A7)

should work

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Martin" wrote in message
...
Hi all,

Newbie question. I want to calculate an overall score
where sometimes I have a NA field (not available score.
In the example below, I would like to calculate SUM Row 1
to Row 7 and only divide by the number of rows with actual
numbers...in this case it would be (13 / 4). Please help.

Thanks.

Martin

Row Score
1 2
2 3
3 NA
4 5
5 3
6 NA
7 NA

Excel calculation
 

Just use the average function for the column and it will
give you the correct value.

=AVERAGE(B1:B7)

1 2
2 3
3 na
4 5
5 3
6 na
7 na
3.25

-----Original Message-----
Hi all,

Newbie question. I want to calculate an overall score
where sometimes I have a NA field (not available score.
In the example below, I would like to calculate SUM Row 1
to Row 7 and only divide by the number of rows with
actual
numbers...in this case it would be (13 / 4). Please help.

Thanks.

Martin

Row Score
1 2
2 3
3 NA
4 5
5 3
6 NA
7 NA







.