Google Spreadsheet Post #1271
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul 03, 2013
user burpootus (http://productforums.google.com/forum/?zx=en1uc39g9p3s#!category-topic/docs/spreadsheets/8ax4_qYFUSQ)
Spreadsheet function help
I am new to Google Doc Spreadsheets. As a learning exercise, I have a spreadsheet to keep track of everything I do to a new vehicle. Column B contains the odometer reading for each item. Column C contains a description of the item. So let's say column C has events such as purchase seat covers, gas, hand wash, purchase floor mats, hand wash, gas, oil change, hand wash, air filter, gas, etc., etc. Now let's say I want to have the spreadsheet figure the gas mileage each time the event is gas, and I have the gallons purchased entered in column D, in the same row as the odometer and the event. So I need the formula to recognize the event is gas, search column C for the previous gas event, associate these two cells with the same row cells in column B, subtract the previous odometer reading from the current one, and finally divide the gallons purchased in column D for the MPG. And this with a constantly varying number of events between each gas event. I assume the root of the solution is to treat column C as an array and search for "gas" and return the indexes of those cells but I haven't figured out how to do this. Thanks.
---
https://docs.google.com/
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Jul 03, 2013
user burpootus (http://productforums.google.com/forum/?zx=en1uc39g9p3s#!category-topic/docs/spreadsheets/8ax4_qYFUSQ)
Spreadsheet function help
I am new to Google Doc Spreadsheets. As a learning exercise, I have a spreadsheet to keep track of everything I do to a new vehicle. Column B contains the odometer reading for each item. Column C contains a description of the item. So let's say column C has events such as purchase seat covers, gas, hand wash, purchase floor mats, hand wash, gas, oil change, hand wash, air filter, gas, etc., etc. Now let's say I want to have the spreadsheet figure the gas mileage each time the event is gas, and I have the gallons purchased entered in column D, in the same row as the odometer and the event. So I need the formula to recognize the event is gas, search column C for the previous gas event, associate these two cells with the same row cells in column B, subtract the previous odometer reading from the current one, and finally divide the gallons purchased in column D for the MPG. And this with a constantly varying number of events between each gas event. I assume the root of the solution is to treat column C as an array and search for "gas" and return the indexes of those cells but I haven't figured out how to do this. Thanks.
---
https://docs.google.com/
Imagine data entry such as this continues through the life of the vehicle, with varying numbers of cells separating each gas event. I want the spreadsheet to automatically calculate MPG.
---
Cells E6, E8, and E13 (every row that contains the "gas" event in column C) should calculate Miles Per Gallon by taking the odometer reading value in the same row in column B and subtracting the previous odometer reading (in the case of E6 this would be B3, E8 it would be B6, and for E13 it would be B8 - always a variable number of rows between each other depending on the events entered), and dividing it by the gallons of gas in column D. Over the lifetime of a vehicle, many events would be recorded and the gas events would always be a differing number of rows apart, preventing me from using a simple, static formula to figure MPG.
-----------------------------------------------------------------------------------------------------------------------------------------------
No comments:
Post a Comment