0

I have a Google form where people track their company mileage and input "Mileage In" and "Mileage Out."

I want to insert a column next to Mileage In and Mileage Out that shows the difference between those numbers, which would be the total miles driven by that user.

I understand that I should be using an array formula and have tried to use one from this article: https://www.benlcollins.com/formula-examples/array-formulas-forms/

I inserted a column next to the two mileage columns and put this formula in the header:

=ArrayFormula(IF(ISBLANK($O$2:$O),"",SUM("$N$2:$N"-"$M$2:$M")))

It doesn't work and returns an error about circular dependency.

I don't understand this formula well enough to continue troubleshooting on my own. Any advice?

0

=ARRAYFORMULA(IF(LEN(O2:O), QUERY(M2:N, "select N-M label N-M''"), ))

0

  • Thank you for the help! When I try to use that formula exactly as written, I get this error: "Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings." – eegatlanta Mar 12 at 21:30
  • I can't share a copy because it would be breaking confidentiality rules at my job, but I can reproduce something similar with dummy data and then post. – eegatlanta Mar 13 at 12:59
  • Here you go. Let me know if you need anything else:docs.google.com/spreadsheets/d/… Please note that THIS document is just a copy of the other sheet, and it is not connected to a google form. However, the real sheet I am using is connected to a google form, which will be continuously updated. Thanks again. – eegatlanta Mar 13 at 13:17
  • your "Mileage In" and "Mileage Out" columns (M, N) are empty. also, where you want that sum (if they wouldn't be empty) ? – user0 Mar 13 at 15:34
-1

={"ARRAYFORMULA IS HERE";ARRAYFORMULA(IF(LEN(P2:P), QUERY(N2:O, "select O-N label O-N''"), ))}

From user0 :D Thank you!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.