Formula to compare different values in different columns based on user input

Hello,

Normally, when I want to compare the difference as a percentage between 2 columns, A (old price) and B (new price), I use a simple formula B/A-1. This gives me the price increase as a percentage between B and A.

My problem is that I have 4 columns (with new prices) and 10 columns (with old prices) and I would like to compare any 1 of the 4 columns compared to any 1 of the 10 columns.

Is there a formula, where I can enter the letter or number of the columns into 2 different cells, it can calculate the difference as a percentage?

The heat is melting my brain and I cannot work it out.

I was thinking something like this, if in cell A1 I enter 4 and in cell B2 I enter 10, the formula calculates column4/column10-1.

Any help very much welcomed.

Thank you!

Hello guys, many thanks for the replies. I've been on vacation but strangely I didn't receive any notifications regarding your replies. I will give them all a try and update you all.

Again, I am truly grateful for those who have tried to help me.

Ciao!

Originally Posted by Kaper

This "A1 I enter 4 and in cell B2 I enter 10, the formula calculates column4/column10-1 " you can easily achiewe with INDIRECT or OFFSET

so lets say your first 4 are new A2:D2, then 10 old E2:N2
in O2 you write 2 to use B2 and in P2 3 to use G2:

Formula:

Please Login or Register  to view this content.

Hello everyone,

Sorry for the late reply. This one worked perfectly. I modified it slightly so that you only need to enter the 2 and the 3 (from the above example) in 2 fixed cells (using $O$2 and $P$2 - another tip I learnt from this fantastic forum) at the top of the sheet (because there are about 800 rows) just to make things simpler.

Many thanks also to sourabhg98 and ChemistB for taking the time and effort to help me.

Enjoy your day!