Multi-Column Formula Shortcut

OKay…...

Pretty straightforward I think......but can nly solve it the LONG way.

Have a dataset (columns B-h) I want to divide by another dataset (columns I-0) (old data vs new data) to get an index. (columns P-V)

Right now - it's all in one table.

Column headings are mostly the same (with exception of old and new designation)

I know I can use the formula tool and set up an equation for every comparison - but surely there is a way to do this en masse?

My example attached is just that - my real data has hundreds of columns (thus why I am looking for a shortcut)

I COULD divide the table into 2 if that helps anything.....

Thanks

Corey

Multi_columnFunction.xlsx

Datasets

Preparation

Accepted answers

CharlieS

I approach this by transforming the table a bit. The example attached below relies on the field names ("OLD", "NEW", "A/B/C/D....") to make the matches/formulas work. Hopefully this gives you a few ideas to get started.

20191114-TableCalculations.yxmd

TomWelgemoed

Hi @CorCor and @CharlieS

This is my take on it - I think it can maybe be done with fewer tools. Think @CharlieS 's solution works great too.

Solution 13.jpg

Solution 13.yxzp

All comments

TomWelgemoed

Hi,

Maybe someone has a better way than me, but I always like to use the transpose tool for this type of problem, so to get two columns and then divide column A by column B.