Formulas Across Fields

[denvic29]

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. 

[MichalM]

@denvic29 

I'd approach this slightly differently. I'd transpose the data so that I have the state name in a single column and the two columns I'd like to use in the formula side-by side. Once calculated, I'd pivot the data back and append to the original data set. Example attached.

[estherb47 · Accepted solution]

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