Lookup - Pro-rate formula
 

I am looking for a formula to include in a spreadsheet for the following:

I have two equal length columns of no more than 50 numbers.

Say:

A B
1.5 9.8
1 11.7
..5 14
0 16.7

I have two reference cells with values 1 and .62.

If I look up column A reference "1" the answer I am looking for in column B
is "11.7" which I can work out using the Lookup function.

With the second number .62, the answer I am looking for needs to be 13.724
which is the pro-rata difference between the values in A and B.

Any help would be appreciated.

Thanks,
Mal

Hi!

D1 = 0.62
E1 = 1

Array entered:

=SUM(LINEST(B1:B4, A1:A4)*D1:E1)

Returns: 13.648

???

Biff

"Mal" wrote in message
...
I am looking for a formula to include in a spreadsheet for the following:

I have two equal length columns of no more than 50 numbers.

Say:

A B
1.5 9.8
1 11.7
.5 14
0 16.7

I have two reference cells with values 1 and .62.

If I look up column A reference "1" the answer I am looking for in column
B is "11.7" which I can work out using the Lookup function.

With the second number .62, the answer I am looking for needs to be 13.724
which is the pro-rata difference between the values in A and B.

Any help would be appreciated.

Thanks,
Mal

Biff,
Thanks for your reply.
I don't think your answer is what I am looking for but it would probably
help if I got my maths correct in the first place. I think the answer should
be 13.448.
What I am saying is if I look "1" up in column A, the result in column B is
exactly 11.7.
Because .62 is not an exact number in column A but fits between .5 and 1,
the answer in column B should fit between 11.7 and 14 which I calculate
should be 13.448.
Any further thoughts?
Thanks,
Mal


"Biff" wrote in message
...
Hi!

D1 = 0.62
E1 = 1

Array entered:

=SUM(LINEST(B1:B4, A1:A4)*D1:E1)

Returns: 13.648

???

Biff

"Mal" wrote in message
...
I am looking for a formula to include in a spreadsheet for the following:

I have two equal length columns of no more than 50 numbers.

Say:

A B
1.5 9.8
1 11.7
.5 14
0 16.7

I have two reference cells with values 1 and .62.

If I look up column A reference "1" the answer I am looking for in
column B is "11.7" which I can work out using the Lookup function.

With the second number .62, the answer I am looking for needs to be
13.724 which is the pro-rata difference between the values in A and B.

Any help would be appreciated.

Thanks,
Mal

Hi!

Well, I'm not a math expert but that's exactly what the LINEST function is
for!

Biff

"Mal" wrote in message
...
Biff,
Thanks for your reply.
I don't think your answer is what I am looking for but it would probably
help if I got my maths correct in the first place. I think the answer
should be 13.448.
What I am saying is if I look "1" up in column A, the result in column B
is exactly 11.7.
Because .62 is not an exact number in column A but fits between .5 and 1,
the answer in column B should fit between 11.7 and 14 which I calculate
should be 13.448.
Any further thoughts?
Thanks,
Mal


"Biff" wrote in message
...
Hi!

D1 = 0.62
E1 = 1

Array entered:

=SUM(LINEST(B1:B4, A1:A4)*D1:E1)

Returns: 13.648

???

Biff

"Mal" wrote in message
...
I am looking for a formula to include in a spreadsheet for the
following:

I have two equal length columns of no more than 50 numbers.

Say:

A B
1.5 9.8
1 11.7
.5 14
0 16.7

I have two reference cells with values 1 and .62.

If I look up column A reference "1" the answer I am looking for in
column B is "11.7" which I can work out using the Lookup function.

With the second number .62, the answer I am looking for needs to be
13.724 which is the pro-rata difference between the values in A and B.

Any help would be appreciated.

Thanks,
Mal