|
MPG Calculations
I have a worksheet used for calculating vehicle fuel economy statistics.
The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
Hi Terry,
For mpg calculations, you don't really need to consider whether the tank was full when you started, unless you have a very small sample of data. All you really need is total miles divided by total gallons. Each time you put petrol in, whether you fill it or not, just record the gallons and the miles. The more miles and gallons you record, the less it will matter what state of fill the tank was in when you began. Regards - Dave. |
MPG Calculations
Based on this data tell me what results you would expect:
......Miles.....Gals.....Filled = Y.....MPG ......1000.......12............................... ... ......1367.......11.........Y..................... . ......1623.......10............................... ... ......1972.......14.........Y................??... ......2200.......13............................... ... ......2500.......10............................... ... ......2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
Biff
The first reading would be (1972-1367)/(10+14) = 25.21. The second would be (2833 - 1972)/(13+10+12) = 24.6. Thanks. Terry "T. Valko" wrote in message ... Based on this data tell me what results you would expect: .....Miles.....Gals.....Filled = Y.....MPG .....1000.......12................................ .. .....1367.......11.........Y...................... .....1623.......10................................ .. .....1972.......14.........Y................??... .....2200.......13................................ .. .....2500.......10................................ .. .....2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
Dave
The vehicles in question are buses with large tanks that do relatively small mileages. I also need to monitor the MPG per trip rather than an overall figure. Terry "Dave" wrote in message ... Hi Terry, For mpg calculations, you don't really need to consider whether the tank was full when you started, unless you have a very small sample of data. All you really need is total miles divided by total gallons. Each time you put petrol in, whether you fill it or not, just record the gallons and the miles. The more miles and gallons you record, the less it will matter what state of fill the tank was in when you began. Regards - Dave. |
MPG Calculations
Ok, based on that sample table I posted...
A1:D1 = column headers Enter this formula in E2 and copy down as needed: =IF(COUNTIF(D$2:D2,"Y")<=1,"",IF(D2="","",ROUND((B 2-LOOKUP(2,1/(D$1:D1="Y"),B$1:B1))/SUM(C2:INDEX(C$1:C2,MATCH("zzz",D$1:D1)+1)),2))) You'll notice that there are some references to the header row. This is intentional. -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff The first reading would be (1972-1367)/(10+14) = 25.21. The second would be (2833 - 1972)/(13+10+12) = 24.6. Thanks. Terry "T. Valko" wrote in message ... Based on this data tell me what results you would expect: .....Miles.....Gals.....Filled = Y.....MPG .....1000.......12................................ .. .....1367.......11.........Y...................... .....1623.......10................................ .. .....1972.......14.........Y................??... .....2200.......13................................ .. .....2500.......10................................ .. .....2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
Biff
That seems to have cracked it! Just for my enlightenment, what is the reference to "zzz"? Is that some form of wildcard? Many thanks! Terry "T. Valko" wrote in message ... Ok, based on that sample table I posted... A1:D1 = column headers Enter this formula in E2 and copy down as needed: =IF(COUNTIF(D$2:D2,"Y")<=1,"",IF(D2="","",ROUND((B 2-LOOKUP(2,1/(D$1:D1="Y"),B$1:B1))/SUM(C2:INDEX(C$1:C2,MATCH("zzz",D$1:D1)+1)),2))) You'll notice that there are some references to the header row. This is intentional. -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff The first reading would be (1972-1367)/(10+14) = 25.21. The second would be (2833 - 1972)/(13+10+12) = 24.6. Thanks. Terry "T. Valko" wrote in message ... Based on this data tell me what results you would expect: .....Miles.....Gals.....Filled = Y.....MPG .....1000.......12................................ .. .....1367.......11.........Y...................... .....1623.......10................................ .. .....1972.......14.........Y................??... .....2200.......13................................ .. .....2500.......10................................ .. .....2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
what is the reference to "zzz"?
That's used to find the last instance of "Y" in column D. ....MATCH("zzz",D$1:D1) How it works... MATCH has 3 arguments to it. The 3rd argument tells it what type of search to use for the lookup value. If the 3rd argument is not specified or omitted (as I have done) it defaults to a match type of 1 meaning the lookup array is sorted and if an exact match is not found it will match the closest value that is less than the lookup value. Using the match type 1, if the lookup value is greater than any value in the lookup array the match will be made at the *last* value in the lookup array that is less than the lookup value. This works for both numeric values and TEXT values. Think of the alphabet. A, B, C,...X, Y, Z Our lookup value is "zzz". The only entries in column D will be the letter "Y". The lookup value "zzz" is greater than "Y" so the match is made at the *last* "Y" in the lookup array. The *last* "Y" is found depending on how far down the column the formula is copied to. This might seem kind of confusing at first but if you read it a few times or try some tests it'll (eventually) make sense! -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff That seems to have cracked it! Just for my enlightenment, what is the reference to "zzz"? Is that some form of wildcard? Many thanks! Terry "T. Valko" wrote in message ... Ok, based on that sample table I posted... A1:D1 = column headers Enter this formula in E2 and copy down as needed: =IF(COUNTIF(D$2:D2,"Y")<=1,"",IF(D2="","",ROUND((B 2-LOOKUP(2,1/(D$1:D1="Y"),B$1:B1))/SUM(C2:INDEX(C$1:C2,MATCH("zzz",D$1:D1)+1)),2))) You'll notice that there are some references to the header row. This is intentional. -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff The first reading would be (1972-1367)/(10+14) = 25.21. The second would be (2833 - 1972)/(13+10+12) = 24.6. Thanks. Terry "T. Valko" wrote in message ... Based on this data tell me what results you would expect: .....Miles.....Gals.....Filled = Y.....MPG .....1000.......12................................ .. .....1367.......11.........Y...................... .....1623.......10................................ .. .....1972.......14.........Y................??... .....2200.......13................................ .. .....2500.......10................................ .. .....2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
MPG Calculations
Biff
Many thanks for the explanation. I get your drift although I need to unscramble the larger formula to make sense of it all bit by bit. I always find my excursions to this forum to be humbling experiences. Although I understand most aspects of Excel pretty well and am more proficient at it that 99% of the people I work with, whenever I come on here 99% of the participants are better at it than I am!!! Good for coming back down to earth I guess! Terry "T. Valko" wrote in message ... what is the reference to "zzz"? That's used to find the last instance of "Y" in column D. ...MATCH("zzz",D$1:D1) How it works... MATCH has 3 arguments to it. The 3rd argument tells it what type of search to use for the lookup value. If the 3rd argument is not specified or omitted (as I have done) it defaults to a match type of 1 meaning the lookup array is sorted and if an exact match is not found it will match the closest value that is less than the lookup value. Using the match type 1, if the lookup value is greater than any value in the lookup array the match will be made at the *last* value in the lookup array that is less than the lookup value. This works for both numeric values and TEXT values. Think of the alphabet. A, B, C,...X, Y, Z Our lookup value is "zzz". The only entries in column D will be the letter "Y". The lookup value "zzz" is greater than "Y" so the match is made at the *last* "Y" in the lookup array. The *last* "Y" is found depending on how far down the column the formula is copied to. This might seem kind of confusing at first but if you read it a few times or try some tests it'll (eventually) make sense! -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff That seems to have cracked it! Just for my enlightenment, what is the reference to "zzz"? Is that some form of wildcard? Many thanks! Terry "T. Valko" wrote in message ... Ok, based on that sample table I posted... A1:D1 = column headers Enter this formula in E2 and copy down as needed: =IF(COUNTIF(D$2:D2,"Y")<=1,"",IF(D2="","",ROUND((B 2-LOOKUP(2,1/(D$1:D1="Y"),B$1:B1))/SUM(C2:INDEX(C$1:C2,MATCH("zzz",D$1:D1)+1)),2))) You'll notice that there are some references to the header row. This is intentional. -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Biff The first reading would be (1972-1367)/(10+14) = 25.21. The second would be (2833 - 1972)/(13+10+12) = 24.6. Thanks. Terry "T. Valko" wrote in message ... Based on this data tell me what results you would expect: .....Miles.....Gals.....Filled = Y.....MPG .....1000.......12................................ .. .....1367.......11.........Y...................... .....1623.......10................................ .. .....1972.......14.........Y................??... .....2200.......13................................ .. .....2500.......10................................ .. .....2833.......12.........Y................??... -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I have a worksheet used for calculating vehicle fuel economy statistics. The columns a A - Date B - Mileometer reading C - Amount of fuel added D - Whether tank full or not ("Y" if full / blank if not) In column E I want to display the Miles Per Gallon (MPG) based upon the most recent fill-up. Hence, if the tank was filled, I need to devise a formula that refers back to when it was last full (ie; when column C last had a "Y" entered) and then subtracts today's mileage from that in column B when the tank was last full. If the tank was not full then column E remains blank until next time it is full. I can only think of using VLOOKUP for this but not quite sure how as there will be many instances of "Y" in column C - I just need the most recent. Any ideas? Thanks. Terry Bennett |
| All times are GMT +1. The time now is 11:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com