Hi Paul
I assume that you only need to calculate the average for row 2, as the
other rows will not make any sense. If you replace the formula in
cell A2 with the following
=if(A5="","",value(A5))
and the same for cells B2 to C2, the fomula in D2 will give the correct
result, as the open cell in C2 will be ignored.
At Neethling
Cape Town
>Date: Thu, 6 Feb 2003 17:30:06 EST
>From: Paul Roeth <[log in to unmask]>
>Subject: More spreadsheet questions
>
>Hi again,
>
>I'm working with Excel 97 and am trying to calculate averages. Given
>the following table...
>
> A B C D
>1 =value(A4) =value(B4) =value(C4)
>2 =value(A5) =value(B5) =value(C5) =AVERAGE(A2:C2)
>3 =value(A6) =value(B6) =value(C6)
>4 JAN 1ST JAN 2ND FEB 1ST
>5 2 4
>6 1/17/03 1/28/03
>
>I'm trying to calculate an annual running average in D2 but note that
>the record for FEB 1ST is null at C5. As a result, I get "#VALUE!"
>populated in C2 (and C6). The value derived at D2 is 2, but in
>reality should be 3. Is there a way to exclude the nulls in this
>arrangement? Note, I'm not Excel savvy which is why I have this set
>up rather crudely. I'm pasting from Access and after pasting the
>data comes out as text, not numeric. Needing to do calculations,
>I came up with this work-around. Plus it doesn't mess up my grids
>when I paste. Still, I need to be able to calculate accurately, and
>the conditions present don't work correctly. Can someone offer help,
>perhaps in the "=AVERAGE(A2:C2)" statement that would exclude the null
>values from the equation? Apologies up front, I don't know that much
>about Excel.
>TIA
>Paul Roeth
***************
Any views expressed in this message are those of the individual sender, and
Sanlam accepts no liability therefore, except where the sender specifically
states them to be those of Sanlam.
Enige sienswyses of stellings wat in hierdie boodskap uitgedruk word is dié
van die individuele afsender, en Sanlam aanvaar geen aanspreeklikheid
daarvoor nie, behalwe waar die afsender uitdruklik vermeld dat dit dié van
Sanlam is.
The NOSPIN Group Promotions is now offering
Mandrake Linux or Red Hat Linux CD sets along
with the OpenOffice CD... at a great price!!!
http://freepctech.com/goodies/promotions.shtml
|
