Hlookup and Interpolation

[Tennisguuy]

I have a situation with a table that I am using a hlookup. The problem is the lookup value might not be in the table and I will need to interpolate for the value that is not in the table.

Below is a copy of the table

10,000 15,000 25,000 50,000 75,000 100,000 150,000 250,000 500,000 750,000 1,000,000
0.007 0.006 0.005 0.004 0.003 0.003 0.002 0.002 0.04 0.05 0.055
0.002 0.001 0.001 0.001 0.001 0.001 0.001 0.002 0.04 0.05 0.055
0.003 0.002 0.002 0.002 0.001 0.001 0.001 0.002 0.04 0.05 0.055
0.001 0.001 0.001 0.001 0.001 0.001 0.001 0.002 0.04 0.05 0.055
0.001 0.001 0.001 0.001 0.001 0.001 0.001 0.002 0.04 0.05 0.055
0.001 0.001 0.001 0.001 0.001 0.001 0.001 0.002 0.04 0.05 0.055

The table is in rows Q7:AA13.

I used the following formula in cell D9 IF(B5<=4,HLOOKUP(B9,'Crime Rating'!Q7:AA13,2,FALSE)) and it works but again my problem is when the value in B9 is not in the table I will get a #N/A error. For example if the value in cell B9 is 30,000

The values in the individual row depend are in column A I was just going to copy down the formula to change the lookup row. for example in row 10 the formula would be IF(B5<=4,HLOOKUP(B9,'Crime Rating'!Q7:AA13,3,FALSE))

If interpolating within multiple row is too complex I can make a separate table with just one row of data for each of the value in column A.

 

[Marcelo Branco]

Well, I was thinking you already knew how to do this. However, in order to get help you have to define how to do the calculations.
My suggestion above: use a Rule of Three

M.

 

[Marcelo Branco]

Complementing my previous post
I believe it will be a long formula, not difficult, but boring to do...Try to create it, and get back if you have problems.

M.

 

[Marcelo Branco]

A suggestion


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Rule of Three​

[/td][td][/td][td][/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

25000​

[/td][td]

-0,001​

[/td][td][/td][td]

0,0048​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

5000​

[/td][td]

-0,0002​

[/td][td][/td][td][/td][/tr]
[/table]

Where
A2 = T7-S7 (the value above 30000 - the value below 30000 in the range Q7:AA7)
A3 = B9 -S7
B2: 0.004 - 0.005 (value in row 2 corresponds to 50000 - value in row 2 corresponds to 25000)
B3: =A3*B2/A2

Result in D2
=HLOOKUP(B9,'Crime Rating'!$Q$7:$AA$13,2)+B3

M.

 

[Tennisguuy]

Marco thank and yes I know how to do an interpolation if I know where the unknown value would fall. In this example of 30,000. However when the value is not listed in the chart and I would need to interpolate I was wondering if there was a formula that could interpret the value without me having to do a manual interpolation. In one instance the value in B9 could be 30,000 or 600,000. What is a Rule of Three.

 

[Marcelo Branco]

Of course i'm not suggesting you do the interpolation manually.
What i'm trying to say is:
Try to create a formula that does the interpolation, i.e., gets the values in A2, A3, B2, B3 in my example post above (Rule of Three)
For example:
To get the value in A2 (above 30000 - below B9) try
=INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)+1)-INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7))

Where:
the part in Blue corresponds to the value above
the part in Black corresponds to the value below

Then in the original formula in D2
=A3*B2/A2
substitutes A2 by the expression above
=A3*B2/(INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)+1)-INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)))

Try the same to get the values in A3 and in B2 - doing so you can create the final formula step by step.

As i said, if you have problems i'll try to help, but try to do it by yourself - i'm also involved in others threads.

M.

 

[Marcelo Branco]

A neat solution using some helper cells to make the understanding and maintenance easier.


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

SearchValue​

[/TD]
[TD]

SearchRow​

[/TD]
[TD]

ExactMatch?​

[/TD]
[TD]

ValueAbove​

[/TD]
[TD]

Corresp. in Row​

[/TD]
[TD]

ValueBelow​

[/TD]
[TD]

Corresp. in Row​

[/TD]
[TD]

Value to add​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

25000​

[/TD]
[TD]

2​

[/TD]
[TD]

Yes​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD]

0,005​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

30000​

[/TD]
[TD]

2​

[/TD]
[TD]

No​

[/TD]
[TD]

50000​

[/TD]
[TD]

0,004​

[/TD]
[TD]

25000​

[/TD]
[TD]

0,005​

[/TD]
[TD]

-0,0002​

[/TD]
[TD]

0,0048​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Search value in B9

Row of interest in C9

Formula in D9 copied down
=IF(ISNUMBER(MATCH(B9,'Crime Rating'!$Q$7:$AA$7,0)),"Yes","No")

Formula in E9 copied down
=IF(D9="Yes","",INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)+1))

Formula in F9 copied down
=IF(D9="Yes","",INDEX('Crime Rating'!$Q$7:$AA$13,C9,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)+1))

Formula in G9 copied down
=IF(D9="Yes","",INDEX('Crime Rating'!$Q$7:$AA$7,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)))

Formula in H9 copied down
=IF(D9="Yes","",INDEX('Crime Rating'!$Q$7:$AA$13,C9,MATCH(B9,'Crime Rating'!$Q$7:$AA$7)))

Formula in I9 copied down
=IF(D9="Yes",0,(B9-G9)*(F9-H9)/G9)

At last...formula in J9 copied down
=HLOOKUP(B9,'Crime Rating'!$Q$7:$AA$13,C9)+I9

M.

 

[Tennisguuy]

Marco thanks for all of your help. Really appreciate it.

 

[Marcelo Branco]

Marco thanks for all of your help. Really appreciate it.

You are very welcome.

Remark: reviewing my message above I noticed that I made a mistake in the formula in I9. By chance the result of the example would be the same, but the correct formula in I9 should be:
=IF(D9="Yes",0,(B9-G9)*(F9-H9)/(E9-G9))

M.
ps: my name is not Marco