Data extraction from cells in a table that changes value based on a number.

Hello everyone,

Attached is a file called Calculator which performs some calculations based upon a value named Base and fills data into a table. The table is dynamic and changes with the base value.

I have left empty columns to fill this base value into the table. The location of this number changes distinctly based upon the number input. The base number usually has 2 decimal digits. When the value for calculation is entered, the table value changes. The base number finds its spot in the table and gets highlighted using conditional formatting. Please enter numbers with 2 decimal places in H2 and see the changes.

One striking pattern in this table is that each cell increases in value. And consequently, when the number slips into the table it has a bigger valued number on the right and a smaller valued number on the left.

Now the problem is that I want two numbers from the right and two numbers from the left to be listed in cell f2,g2 for smaller and j2,k2 for the bigger number.

Please help.

It sounds complicated but is quite simple once the attachment is viewed.

Thanking everyone.

Originally Posted by martindwilson

cant you just use
=INT(Base)-1 and -2
=INT(Base)+1 and +2
?

Yes certainly I could use that but the formulae are a little more complex than shown. It involves reducing/increasing numbers after taking square roots, by certain percent and then re-squaring etc. I have just shown a simple version of the formula to be used and as an example.

Originally Posted by DonkeyOte

Assuming the values being returned are always numbers then perhaps:

F2: =LARGE(IF($B$6:$R$36<$H$2,$B$6:$R$36),2-(COLUMNS($F2:F2)-1))
confirmed with CTRL + SHIFT + ENTER
copied to G2

J2: =SMALL(IF($B$6:$R$36>$H$2,$B$6:$R$36),COLUMNS($J2:J2))
confirmed with CTRL + SHIFT + ENTER
copied to K2

Thanks a lot for your help

Yes they are all numbers, but the formula you mentioned does not seem to work. It gives the #value! error. Kindly advise.

Originally Posted by DonkeyOte

Did you commit the formulas as Arrays ? (ie with CTRL + SHIFT + ENTER not just Enter).

If so, and you're still having problems, post a sample with formulae in place which illustrates as such.

Yes I did it couple of times. Attached is the updated file.

Thanks a lot for helping out.

Originally Posted by DonkeyOte

None have been entered as Arrays.

To reiterate - when you commit the formula you must do so with CTRL + SHIFT + ENTER not just Enter as you would "normally".

Oh Ok. Thanks it worked very well.

Am sincerely grateful.