Solved: Whats the value of y given X

[Pete]

See attached workbook...
Hi Experts

Need a macro to work out the value of a cell if it falls within a certain range of data......

Looking a row 3 and 4 column d onwards....

look at the explain in the workbook and you' will see.......

i need to base the macro on 4 quartiles.......

then take the difference between the two y values and then mulitple it by the quartile the new x value falls into...

[RonMcK]

Pete,

I'm looking at your x, y series. I can interpolate a y value given the series and an x value. How do you see/want quartiles to enter into the computation? (My brain is a bit tired at the moment.)

Thanks

[Pete]

I'm looking at your x, y series. I can interpolate a y value given the series and an x value.- this is fine - only this part

How do you see/want quartiles to enter into the computation? (My brain is a bit tired at the moment.) - forget this i was being a complete idiot when thought of this

[Pete]

ok, i have tried using the follwoing formula still no positive result

=HLOOKUP(ROUND(D10,-1),$D$3:$L$4,2)+(HLOOKUP(MIN(100,ROUND(D10+10,-1)),$D$3:$L$4,2)-HLOOKUP(ROUND(D10,-1),$D$3:$L$4,2))*MOD(D10,10)/10

[RonMcK]

Pete,

I have a solution for you to consider. There may be a better one but this one works. I created some named ranges and I added two rows to your table, one above and the other below, each numbered 1, 2, 3, ... 9. I named your x, y and the new bottom row as Table1 and the new top row, your x and y rows as Table2. Here is the worksheet formula that computes your y value:
[vba]=IF(OR(x<$D$3,x>$L$3),"out of range",(x-HLOOKUP(HLOOKUP(x,Table1,3,1),Table2,2,0))/ _
(-1*HLOOKUP(HLOOKUP(x,Table1,3,1),Table2,2,0)+HLOOKUP(HLOOKUP(x,Table1,3,1)+1 ,Table2,2,0))* _
(-1*HLOOKUP(HLOOKUP(x,Table1,3,1),Table2,3,0)+HLOOKUP(HLOOKUP(x,Table1,3,1)+1 ,Table2,3,0))+ _
HLOOKUP(HLOOKUP(x,Table1,3,1),Table2,3,0))[/vba]
Attached is an updated copy of your workbook.

[Pete]

thanks.....it work...finally

[RonMcK]

thanks.....it work...finally

Pete,

You're welcome. This solution doesn't use VBA (macros) so the button isn't needed. Let me know if you still want a solution using VBA.

Cheers!