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
07-28-2008, 09:36 AM
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
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
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
07-29-2008, 06:31 AM
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:
=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))
Attached is an updated copy of your workbook.
thanks.....it work...finally
RonMcK
07-29-2008, 06:55 AM
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.