Monday, May 11, 2015

yogi_Compute Row By Row In Column G MilesDriven From Mileage Log Recorded In Column B Of 'Form Responses 1' Sheet


                                           Google Spreadsheet   Post  #1953
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    May 11, 2015
post by mrludden:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/d3DXmC6EjTQ
Help with performing subraction between 2 adjacent values in the same column
Hi

here is my sheet. In column G of 'Form Responses 1' I am calculating the difference between the values in the same row of column B and the previous row of column B. However, the formula I am using also creates an entry on the next row of column G with a large negative number to deal with the last row. This is probably because I am not as familiar with the OFFSET formula I used to try and deal with the issue. My best solution at this point is to create a query() on a new blank sheet (which I've created but haven't done the query yet) and simply select the columns where column b is not null. Is this the best answer or is there a more efficient formula that will give me the difference between the adjacent rows without having this extra value? You will see I have the same issue in column J as well. Also, is IFERROR(IF()) less efficient than IF(ISBLANK()) ? I usually use the second version and I don't remember why I used the IFERROR instead this time. It seems that the arrayformulas take a little longer to populate on this particular sheet and I had a hunch that this was why so any info there is also much appreciated.

Thanks,
Mike 
-----------------------------------------------------------------------------------------------------------------------------------