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.

 

[timfritsch]

Remove the range lookup FALSE statement from your HLOOKUP. When you use FALSE, you are looking for an exact match. Formula should be =IF(B5<=4,HLOOKUP(B9,Q7:AA13,2))

Hope this helps.


Tim

 

[Tennisguuy]

Thanks yes I tried that and it will give me a value the but the will be for the closet value in the table for value in B9

 

[Marcelo Branco]

Hi Ken

As the values in Q7:AA7 are in ascending order you can try HLOOKUP with an approximate match: last parameter (Range_lookup) = 1 or omitted.
=HLOOKUP(B9,'Crime Rating'!$Q$7:$AA$13,2)

Take a look at
https://support.office.com/en-us/article/HLOOKUP-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f

M.

 

[Marcelo Branco]

Thanks yes I tried that and it will give me a value the but the will be for the closet value in the table for value in B9

I hadn't seen this.
How do you want to do the interpolation? What is the desired result when B9 = 30000?

M.

 

[Tennisguuy]

It would be a basic interpolation but would depend on the value entered. For example if 30,000 was the lookup value the interpolated value would use the value between 25,000 and the value in 50,000 which is probably be .0045

 

[Marcelo Branco]

A Rule of Three?

Something like
=IF(B5<=4,IFERROR(HLOOKUP(B9,'Crime Rating'!$Q$7:$AA$13,2,FALSE),"calculate interpolation"))

M.

 

[Tennisguuy]

I was looking to have the interpolation done within the formula as opposed to a statement alerting that it needs to be done because once the rate is determined it is used within another formula.

 

[Marcelo Branco]

I was looking to have the interpolation done within the formula as opposed to a statement alerting that it needs to be done because once the rate is determined it is used within another formula.

Yes, i'm suggesting you replace
"calculate interpolation"
by a formula, according to the appropriate criteria to do the calculations.

M.

 

[Tennisguuy]

That's what I wanted help with is the interpolation formula.