Multiple Offset and Match Functions

Alright, as I had posted earlier, I'm still having some issues with my formulas and since this is a completely different issue, I felt it might deserve it own thread. Am I right or am I right . Heres the deal; we're only going to look at the last part of the IF function. This is linear interpolation at the max I might add. What I want is for this function to take look something like this (in lay-mans terms)

((new.number-low.oldnumber)/(high.oldnumber-low.oldnumber)*(high.oldvalue-low.oldvalue))+low.oldvalue

How I have it set up is like this:

=IF(D54=C6,D6,IF(D54=0,(C30/C6)*D6,((C30-D54)/(H54-D54)*(OFFSET(C6,MATCH(H54,C6:C25,0),1,1,1)-OFFSET(C6,MATCH(D54,C6:C25,0),1,1,1))+OFFSET(C6,MATCH(D54,C6:C25,0),1,1,1))))

C6: 500
C7: 1000
C8: 2000
C9: 3000
C10: 4000
C11: 5000
C12: 6000
C13: 7000
C14: 8000
C15: 9000
C16: 10000
C17: 11000
C18: 12000
C19: 13000
C20: 14000
C21: 15000
C22: 16000
C23: 17000
C24: 18000
C25: 19000

C30: 900

D6: 0.8880

D54: 500

H54: 1000

Is what I'm asking impossible, or am I just missing something out of my code? Thanks you guys! You've been a hell of a lot of help so far

Hi

Using your example numbers, can you fill out the numbers you would expect to be in your formulas, and the cell references that contain those numbers.

Also, what is in column D (apart from D6)? Your offset references column D.

rylo

D6: 0.8880

The thing that makes this difficult is that the cell locations are constantly changing. For the low and high cell functions, I have this:

Low:
=IF(SMALL(C6:C25,1)>C30,0,IF(C30>LARGE(C6:C25,1),LARGE(C6:C25,1),VLOOKUP(C30,C6:C25,1,TRUE)))

High:
=IF(SMALL(C6:C25,1)>C30,SMALL(C6:C25,1),IF(C30>LARGE(C6:C25,1),C30,IF(1=COUNTIF(C6:C25,C30),C30,OFFSET(C6,MATCH(C30,C6:C25,1),0,1,1))))

Originally Posted by rylo

Using your example numbers, can you fill out the numbers you would expect to be in your formulas, and the cell references that contain those numbers.
rylo

To be blunt, no. Reason being is that everything is constantly changing in the environment I'm making for these numbers and values, which is what makes it hard. Only thing that stays constant throughout is the column number... and even that changes later on in the code.

Figured out what my problem was:

old code:

=IF(D54=C6,D6,IF(D54=0,(C30/C6)*D6,((C30-D54)/(H54-D54)*(OFFSET(C6,MATCH(H54,C6:C25,0),1,1,1)-OFFSET(C6,MATCH(D54,C6:C25,0),1,1,1))+OFFSET(C6,MATCH(D54,C6:C25,0),1,1,1))))

new code:

=IF(C30=C6,D6,IF(D54=0,(C30/C6)*D6,((C30-D54)/(H54-D54)*(OFFSET(C6,MATCH(H54,C6:C25,0)-1,1,1,1)-OFFSET(C6,MATCH(D54,C6:C25,0)-1,1,1,1))+OFFSET(C6,MATCH(D54,C6:C25,0)-1,1,1,1))))

The section of code in red was pretty much redundant, and one I got this worked out I went ahead and took a look at what the code was doing to still give me the wrong value. marked in blue is -for whatever reason- a section of code that -when found- would revert to the cell to the right and down one. So to counter this, I just subtracted one from it and all was gravy

Still not completely sure why it does this, but maybe someone else can break this down a little further for me?