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?

Improve this question
0

2 Answers 2

Reset to default
0 

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

0

Improve this answer
6
  • 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, 2019 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, 2019 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, 2019 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, 2019 at 15:34
  • docs.google.com/spreadsheets/d/…
    – user0
    Mar 13, 2019 at 15:46
-1

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

From user0 :D Thank you!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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