Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Values
I have a workbook with with the following data:
Date Premium Paid, Premium Paid Amount, Days in Force (in a column numbered from 1 - 365), % of Retained Premium (one column to the right - a value for each day 1-365) I would like to make a report to show the balance of the premium that is at risk. I would like to pick up the Date the Premium was Paid and subtract today's date. I would like the result to match the number in the column of days from 1-365 and pick up the corresponding % of Retained Premium. Then, I would like to take the result and subtract it from 100 to get the % of Premium at risk. Then I would like to take the result and multiply it by the Premium Paid Amount. This should give me the result I would like. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Values
Can u supply any data/example to tinker with?
"Dale" wrote: I have a workbook with with the following data: Date Premium Paid, Premium Paid Amount, Days in Force (in a column numbered from 1 - 365), % of Retained Premium (one column to the right - a value for each day 1-365) I would like to make a report to show the balance of the premium that is at risk. I would like to pick up the Date the Premium was Paid and subtract today's date. I would like the result to match the number in the column of days from 1-365 and pick up the corresponding % of Retained Premium. Then, I would like to take the result and subtract it from 100 to get the % of Premium at risk. Then I would like to take the result and multiply it by the Premium Paid Amount. This should give me the result I would like. Any help would be appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup Values
Sure I can:
In Sheet 1 I have the following data: Column A in rows 3 - 367 the numbers 1 through 365 (representing the number of Days the Policy is in force). In Column B in rows 3-367 I have the numbers that represent the percent of retained policy if cancelled from 10.2% on day 1 to 100% on day 365. (there is no constant sequence) In Sheet 2 I have the following data: Date Premium has been paid and the Premium paid amount. In Sheet 3 is where I would like to create the report that I described in my first question. I hope that will help. "Jill" wrote: Can u supply any data/example to tinker with? "Dale" wrote: I have a workbook with with the following data: Date Premium Paid, Premium Paid Amount, Days in Force (in a column numbered from 1 - 365), % of Retained Premium (one column to the right - a value for each day 1-365) I would like to make a report to show the balance of the premium that is at risk. I would like to pick up the Date the Premium was Paid and subtract today's date. I would like the result to match the number in the column of days from 1-365 and pick up the corresponding % of Retained Premium. Then, I would like to take the result and subtract it from 100 to get the % of Premium at risk. Then I would like to take the result and multiply it by the Premium Paid Amount. This should give me the result I would like. Any help would be appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use LOOKUP to return a range of values, then SUM values? | Excel Worksheet Functions | |||
Lookup on 2 Values | Excel Worksheet Functions | |||
Advanced Lookup (lookup for 2 values) | Excel Worksheet Functions | |||
lookup values to sum | Excel Worksheet Functions | |||
How do I lookup and return different values when the lookup value. | Excel Discussion (Misc queries) |