|
how to formulate this easily???
Hi All,
TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
Hi
If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
Try this:
=INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((IND EX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1))/(INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)))*(A$2-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)) Sandy, Executing your solution gave a value of approx $74,000 for 90 days whereas the table has a value of $90,000. The data is non-linear and I beieve your solution only works if it is a straight line. "Sandy" wrote: Hi If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
Correction ...
I needed to add A9 and B9 with 0 (zero) in each cell. "Toppers" wrote: Try this: =INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((IND EX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1))/(INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)))*(A$2-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)) Sandy, Executing your solution gave a value of approx $74,000 for 90 days whereas the table has a value of $90,000. The data is non-linear and I beieve your solution only works if it is a straight line. "Sandy" wrote: Hi If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
thanks Toppers,
I follow the formula and add 0,0 on first row of data amount,days... i got some problem on the last result cell e.g. J3 e.g. last ref cell : B19 = 302 lookup cell J2=302 result cell J3=#REF! i really don't know what happen here! I verify the ranges in the formula by auditing tools! Please verify the formula ! gracias and regards... -- ***** birds of the same feather flock together.. "Toppers" wrote: Correction ... I needed to add A9 and B9 with 0 (zero) in each cell. "Toppers" wrote: Try this: =INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((IND EX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1))/(INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)))*(A$2-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)) Sandy, Executing your solution gave a value of approx $74,000 for 90 days whereas the table has a value of $90,000. The data is non-linear and I beieve your solution only works if it is a straight line. "Sandy" wrote: Hi If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
Extend range to include row 20:
=INDEX($A$9:$A$20,MATCH(A$2,$B$9:$B$20,1))+((INDEX ($A$9:$A$20,MATCH(A$2,$B$9:$B$20,1)+1)-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)))/(INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1)+1)-INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1))))*(A$2-INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1))) "driller" wrote: thanks Toppers, I follow the formula and add 0,0 on first row of data amount,days... i got some problem on the last result cell e.g. J3 e.g. last ref cell : B19 = 302 lookup cell J2=302 result cell J3=#REF! i really don't know what happen here! I verify the ranges in the formula by auditing tools! Please verify the formula ! gracias and regards... -- ***** birds of the same feather flock together.. "Toppers" wrote: Correction ... I needed to add A9 and B9 with 0 (zero) in each cell. "Toppers" wrote: Try this: =INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((IND EX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1))/(INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)))*(A$2-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)) Sandy, Executing your solution gave a value of approx $74,000 for 90 days whereas the table has a value of $90,000. The data is non-linear and I beieve your solution only works if it is a straight line. "Sandy" wrote: Hi If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
how to formulate this easily???
thanks Toppers,
thats a relief... regards, driller -- ***** birds of the same feather flock together.. "Toppers" wrote: Extend range to include row 20: =INDEX($A$9:$A$20,MATCH(A$2,$B$9:$B$20,1))+((INDEX ($A$9:$A$20,MATCH(A$2,$B$9:$B$20,1)+1)-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)))/(INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1)+1)-INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1))))*(A$2-INDEX($B$9:$B$20,MATCH(A$2,$B$9:$B$20,1))) "driller" wrote: thanks Toppers, I follow the formula and add 0,0 on first row of data amount,days... i got some problem on the last result cell e.g. J3 e.g. last ref cell : B19 = 302 lookup cell J2=302 result cell J3=#REF! i really don't know what happen here! I verify the ranges in the formula by auditing tools! Please verify the formula ! gracias and regards... -- ***** birds of the same feather flock together.. "Toppers" wrote: Correction ... I needed to add A9 and B9 with 0 (zero) in each cell. "Toppers" wrote: Try this: =INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((IND EX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1))/(INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1))-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)))*(A$2-INDEX($B$9:$B$19,MATCH(A$2,$B$9:$B$19,1)+1)) Sandy, Executing your solution gave a value of approx $74,000 for 90 days whereas the table has a value of $90,000. The data is non-linear and I beieve your solution only works if it is a straight line. "Sandy" wrote: Hi If I am reading what you want correctly, then I would suggest: Copy the range A10:B20 Paste special into -say - A8 after ticking the 'Transpose' box. In A3 use =A8/A9*A2 and then drag the formula along the rest of the row. Sandy "driller" wrote in message ... Hi All, TIA... I need to extract the corresponding intercept of the days with the amount. for cash flow variance e.g. I have one row with 10 columns - each cell contains days - A2:J2 25 50 65 90 115 135 150 180 200 215 then I have a 2 columns of data first column stores the amount, while the second column stores the days... A10:B20 $30,000 30 $60,000 60 $90,000 90 $100,000 121 $150,000 152 $160,000 180 $200,000 210 $220,000 241 $250,000 271 $300,000 302 to place formulated result in A3:J3, by searching the direct interpolated amount between the given data e.g. since A2=25 and A10=$30,000 and B10=30, hence A3=25,000 also since B2=50 then B3= $50,000 hope there is one formula for this type of question. regards n gracias -- ***** birds of the same feather flock together.. |
| All times are GMT +1. The time now is 04:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com