how to formulate this easily???

[Guest]

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

 

[Sandy]

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

 

[Guest]

Try this:

=INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((INDEX($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.

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

 

[Guest]

Correction ...

I needed to add A9 and B9 with 0 (zero) in each cell.

Try this:

=INDEX($A$9:$A$19,MATCH(A$2,$B$9:$B$19,1)+1)+((INDEX($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.

 

[Guest]

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...

 

[Guest]

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)))

 

[Guest]

thanks Toppers,
thats a relief...

regards,
driller