Hey guys,
So i have a pretty complicated lookup im trying to perform, but its based on a formula, so i think its possible. I need this to be performed as a cell function, not as a VBA macro as it will be used as a just in time lookup, if that makes any sense.
So i have a table with the following structure. Proof is the index column, Temperature is the column header
Rules:
Temperature only increases by increments of 2 degrees
Proof increases by increments of 5 proof
A1 B1 C1 D1 E1 F1
--------------------------------------------
Temperature 22 24 26 28 30 etc
Proof
5
10
15
20
25
30
etc
etc
etc
200
I also have my 2 proof and temperature input cells and the results cell, lets call them AA1, AB1, AC1 respectively
Now, if the temperature i am looking up is an even number, no problems because MATCH() will find it and reference it in the multi-key lookup as such
VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))
However, if the temperature is NOT an even number, then the formula becomes more complicated.
Here is the description of the formula i am trying to create.
Where the spirits to be corrected are of an odd temperature, 1/2 of the difference, if any, between the factors for the next higher and lower temperature, should be added to the factor for the next higher temperature.
example: it is desired to correct a spirit of 180 proof at 51 degrees
1.006 ( which is the value in the table for 50 degrees) - 1.005 ( which is the value in the table for 50 degrees) = .001 / 2 = .0005
.0005 + 1.005 ( which is the value in the table for 50 degrees) = 1.0055 ( which would be the correction factor at 51 degrees)
You can see its very confusing just to begin with.
I guess i am looking for help with the logic a bit.
So far i have
If(find if temp is even using MOD()) then
VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))
else
this is the part i am having trouble visualizing
Any help is greatly appreciated excel gurus!
So i have a pretty complicated lookup im trying to perform, but its based on a formula, so i think its possible. I need this to be performed as a cell function, not as a VBA macro as it will be used as a just in time lookup, if that makes any sense.
So i have a table with the following structure. Proof is the index column, Temperature is the column header
Rules:
Temperature only increases by increments of 2 degrees
Proof increases by increments of 5 proof
A1 B1 C1 D1 E1 F1
--------------------------------------------
Temperature 22 24 26 28 30 etc
Proof
5
10
15
20
25
30
etc
etc
etc
200
I also have my 2 proof and temperature input cells and the results cell, lets call them AA1, AB1, AC1 respectively
Now, if the temperature i am looking up is an even number, no problems because MATCH() will find it and reference it in the multi-key lookup as such
VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))
However, if the temperature is NOT an even number, then the formula becomes more complicated.
Here is the description of the formula i am trying to create.
Where the spirits to be corrected are of an odd temperature, 1/2 of the difference, if any, between the factors for the next higher and lower temperature, should be added to the factor for the next higher temperature.
example: it is desired to correct a spirit of 180 proof at 51 degrees
1.006 ( which is the value in the table for 50 degrees) - 1.005 ( which is the value in the table for 50 degrees) = .001 / 2 = .0005
.0005 + 1.005 ( which is the value in the table for 50 degrees) = 1.0055 ( which would be the correction factor at 51 degrees)
You can see its very confusing just to begin with.
I guess i am looking for help with the logic a bit.
So far i have
If(find if temp is even using MOD()) then
VLOOKUP(AA1,A:Z,(MATCH(AB1,A:Z,1)))
else
this is the part i am having trouble visualizing
Any help is greatly appreciated excel gurus!