Adjusting results by comparing to a table?

Moshpirit · September 12, 2021, 6:51pm

I’m asking because I’m trying to automatize the process of getting the Water content (page 11 - point 6 of the Harmonised methods by The International Honey Commission).

The idea would be: having the table in a hidden sheet and comparing the input (Refractive Index) to the table to get the Water content. It would be even better if I could do that with a whole column of inputs and get the results in another one.

The problem: some of the inputs are not the exact numbers, they might be different (eg: instead of 1.5002, I get 1.5005 in the lab, so the water content would be between 14.6 (1.5002) and 14.4 (1.5007), so I want it to get 14.5 instead of 14.4 or 14.6)

karolus · September 12, 2021, 8:51pm

Hallo

=TREND(water ; refrac ; 1.5005 ; 1 )

where ‘water’ is the one Column_range with the Values of Water Content.
and ‘refrac’ the corresponding Column of Refractive Index.
Data_from_the_Honey_comb.ods (14.1 KB)

Moshpirit · September 12, 2021, 9:59pm

That’s amazing, thanks!! I’ll definitely look into the formula to get more information!

Moshpirit · September 13, 2021, 5:22pm

Hi again, I saw that the original formula isn’t lineal (since it contains a logarithm):

W = [ -0.2681 - log (RI -1) ] / 0,002243

I’m not sure if the function would still work and if not, how to fix it.

karolus · September 13, 2021, 5:38pm

Honey_comb_with_xy_diagramm.ods (18.7 KB)
The xy_diagram produced from your lookup-data looks straight linear!

Moshpirit · September 13, 2021, 6:19pm

Seems like it’s very linear for values close to 1,5 but if we amplify it, if we use values from 1 to 4 we can see a very clear curve:
Honey_comb_with_xy_diagramm.ods (18.0 KB)