Formulas Across Fields

Question

In my workflow, I have combined two datasets into one file leaving me with two columns for every state. I have formatted the file to have the columns for each state next to each other. I am trying to write a formula to create a new field for each state and then insert it grouped with the other two columns for the state. Is there a way to write a formula to do the same thing after each two columns (ex. column 2 less 3, then column 4 less 5)? I have attached in Excel what I am trying to have happen in green font. I know I could write multiple formulas referencing each of the columns, but I am not familiar is there is a way to write a formula based on column patterns.

Copy of Test Output for Question.xlsx

estherb47 · Accepted Answer

Hi @denvic29

Here is one solution. I've done a few transpose and crosstab, one to get the data in a format where we can summarize (by turning the return column to a negative number, and then adding together), and then one at the end for order purposes.

This should work even as you add in more states.

Let me know if this works.

Cheers!

Esther