Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Value between 2 dates | Excel Worksheet Functions | |||
How do I lookup a value in a array that is not in ascending order | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula Question... LookUP | Excel Discussion (Misc queries) | |||
lookup formula help | Excel Worksheet Functions |