Excel calculation

[Martin]

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

 

[Frank Kabel]

Hi
use
=AVERAGE(A1:A7)

Average will ignore text fields

 

[Niek Otten]

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

 

[Bob Phillips]

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)

 

[tony]

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