Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a formula to perform multiple functions.
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. -- Steven. In God we trust, all others we virus scan. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a formula to perform multiple functions.
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a formula to perform multiple functions.
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 "elusiverunner" wrote in message ... 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. -- Steven. In God we trust, all others we virus scan. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using a formula to perform multiple functions.
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 "Biff" wrote in message ... 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 "elusiverunner" wrote in message ... 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. -- Steven. In God we trust, all others we virus scan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying a formula to multiple worksheets | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Multiple Functions IF, AND, COUNTIF, MATCH | Excel Worksheet Functions | |||
Formula to return cell contents based on multiple conditions | Excel Worksheet Functions |