Extrapolate Values between 2 Rows

[TheTiredEngineer]

I have this table of data:

	pH
Temp	6​	7	8	9​	10
0.5​	12​	12​	12​	12​	90​
5​	8​	8​	8​	8​	60​
10​	6​	6​	6​	6​	45​
15​	4​	4​	4​	4​	30​
20​	3​	3​	3​	3​	22​
25​	2​	2​	2​	2​	15​

And Im trying to get a formula that can extrapolate pH values for between two rows. So say my temperature is 12C, it doesnt fit exactly into one of the rows, so it needs to extrapolate based on the values in this table already. I figure that I have to do a Match to find the pH column that I need (though thats not working either), I just need help with the extrapolation.

=Match(pH,A1:A5)

 

[Kaper]

1) make sure your numbers are numbers, not textx, which just look like numbers.
I suspect some of them are texts, as they are lest justified. Like 7, 8 and 10.

2) as for problem definition/descriptiom: You have 2 values: pH and temperature and would like to read a value from the table (with possible interpolation between 2 rows if temperature is not exactly 0.5, 5, 10 ...).

So for exact case, like pH=7 and T=15 the result is 4,
and you would like for pH=7 and T=12.5 the result 5

Is that right?

 

[Asbestos_Jen]

I used your data to create a scatter chart for the data with x=temp and y=values below the pH, and then added an exponential trendline (pH 6 to pH9 are the same as each other, and pH 10 has a different equation). I pulled the slope equation from the chart and got the following:

2024-08-29.xlsx
	C	D	E	F	G	H	I
4	Temp/pH	6	7	8	9	10
5	0.5	12	12	12	12	90
6	5	8	8	8	8	60
7	10	6	6	6	6	45
8	15	4	4	4	4	30
9	20	3	3	3	3	22
10	25	2	2	2	2	15
11		y = 12.016e-0.071x				y = 90.229e-0.072x
12
13	Calculate	for pH 6-9	for pH 10
14	input x (temp)	calc y	calc y
15	0.5	11.6	87.0
16	1	11.2	84.0
17	2	10.4	78.1
18	3	9.7	72.7
19	4	9.0	67.7
20	5	8.4	63.0
21	6	7.8	58.6
22	10	5.9	43.9
23	12	5.1	38.0
24	15	4.1	30.6
25	18	3.3	24.7
26	20	2.9	21.4
27	25	2.0	14.9
Sheet1

Cell Formulas
Range	Formula
D15:D27	D15	=12.016*EXP(-0.071*$C15)
E15:E27	E15	=90.229*EXP(-0.072*$C15 )

 

[Kaper]

Well, the exponential trendline seems to be a good idea. But you will not get exactly the same values for data already in table.

So as I prepared linear interpolation method, I'll present it below:

After I copied data from your post indeed some of "numbers" were texts. You can easily check it out by changing format to numbers with 1 decimal digit. Numbers will follow this formatting, while texts will remain unchanged (with no decimal digit).

Proposition of formula for a location of a table as shown on a screenshot, and using linear interpolation:

=LET(mycol,MATCH(I1,B1:F1,0),myrow,MATCH(I2,A$2:A$7,1),ltemp,INDEX(A2:A7,myrow),utemp,INDEX(A2:A7,myrow+1),((utemp-I2)*INDEX(B2:F7,myrow,mycol)+(I2-ltemp)*INDEX(B2:F7,myrow+1,mycol))/(utemp-ltemp))

Or the same formula written in a structured way:

=LET(
  mycol, MATCH(I1,B1:F1,0),
  myrow, MATCH(I2,A$2:A$7,1),
  ltemp,  INDEX(A2:A7,myrow),
  utemp, INDEX(A2:A7,myrow+1),
  ((utemp-I2)*INDEX(B2:F7,myrow,mycol)+(I2-ltemp)*INDEX(B2:F7,myrow+1,mycol))/(utemp-ltemp)
)

Of course instead of LET formula you can calculate helper values in extra cells and then refer to them in final formula.

 

[TheTiredEngineer]

I'll take a stab at this tomorrow. I got so overwhelmed with this rabbit hole today that I put it aside for the day.