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.