Using a formula to perform multiple functions.

[Guest]

Hello Everybody,

I have a request that I hope will not be too difficult and is also possible.
The project I am performing is a personal one and is in relation to
automobile milage. In column "C" I have the speedometer reading at the end
of each week. What I want to do, and this is in one column, to subtract the
current speedometer reading from the previous reading and then each week
average the readings.

I want the column to have the average weekly distance in it each week for
the year to date. Say for week one the distance travelled is 400 the next
week is 450 so in the space next to the second week I would like the average
of 400 and 450 which is 425 then if week three is 300 the average of the
three numbers would be 383.3 and so on.

So in summary the formula must first work the difference between the two
distances and then find the average of all distances. In the cells where
there has been no calculation made it must remain blank until the time it is
used.

 

[Aqib Rizvi]

Steven
Week on starts from Row 3,.


Week Cummulative Run KM Average

1 400 =IF(ISBLANK(B3),"",+B3-B2) =IF(ISBLANK(C3),"",AVERAGE(C$3:C3))
2 850 =IF(ISBLANK(B4),"",+B4-B3) =IF(ISBLANK(C4),"",AVERAGE(C$3:C4))
3 1150 =IF(ISBLANK(B5),"",+B5-B4) =IF(ISBLANK(C5),"",AVERAGE(C$3:C5))
4 =IF(ISBLANK(B6),"",+B6-B5) =IF(ISBLANK(C6),"",AVERAGE(C$3:C6))
5 =IF(ISBLANK(B7),"",+B7-B6) =IF(ISBLANK(C7),"",AVERAGE(C$3:C7))
TOTAL

the answers would be


Week Cummulative Run KM Average

1 400 400 400
2 850 450 425
3 1,150 300 383
4 383
5 383
TOTAL


Best Regards
Aqib Rizvi

 

[Biff]

Hi!

You describe 2 different things in your first 2 paragraphs.

In the first you describe what would be the average difference and in the
second you describe what would be a cumulative average.

Biff

 

[Biff]

Ok, after some more thought I think I understand what you want.

Assume:

C1 = header = Odometer reading
C2 = initial odometer reading = 32,000
C3 = 1st week reading = 32,400
C4 = 2nd week reading = 32,850
C5 = 3rd week reading = 33,150
etc
etc

Enter this formula in D3 as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER):

=IF(C3="","",AVERAGE(C$3:C3-C$2:C2))

Copy down as needed.

Biff

Hi!

You describe 2 different things in your first 2 paragraphs.

In the first you describe what would be the average difference and in the
second you describe what would be a cumulative average.

Biff