Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I set up a repeating formula in Excel?
I am trying to set up a database for life insurance premiums. Basically, the
rate is 10 times the expense constant plus 36 dollars. The expense constant is different for each age. The formula I am typing in looks like this: =(10*10.65)+36. The 10.65 in this example is the expense constant. I am trying to set up a table in which the basic formula (10*___)+36 is already entered, and when I click on a cell all I need to type in is the expense constant. When I use the format painter, I am still having to go into each formula, highlight the expense constant, and clear it out. Is this possible? |
#2
|
|||
|
|||
Hi Sabrina,
Let's start by clearing away some underbrush. Using the Format Painter is fundamentally different from editing a formula, which I think is what you're after. The Format Painter is merely a shortcut to help you define how the contents of a cell appear. So, if cell A1 is formatted to show its contents as a Currency format, you could do this: 1. Select cell A1. 2. Click the Format Painter button. 3. Select another cell, such as D12, that you want to have the Currency format. Then, the Format Painter will display the contents of cell D12 in Currency format. The main point is that the Format Painter has no effect on the contents of a cell, just on how it is displayed. All you're doing is copying a format from one cell to another. Now, the usual way to do what you're describing -- changing the expense constant according to the value of age -- is to use a lookup table. You might have age ranges in column A and the associated expense constant in column B. If you do it that way, you need not edit your basic formula, just present it with a different age. Suppose your age-and-constant lookup table occupies A1:B10, and a customer's actual age is in cell C1. Your formula could look like this: =(10*VLOOKUP(C1,A1:B10,2,TRUE))+36 When you change the value of Age in cell C1, the results of the formula change accordingly. The number 2 in the formula tells Excel to return the value it finds in the 2nd column of the lookup table, once it's found the appropriate Age row in the table. Note that the third argument to the VLOOKUP function, which I've given as TRUE, could also be FALSE -- it depends on how you've structured your lookup table, and whether the expense constant applies to a range of ages, or whether it changes with each year that the customer comes closer to shuffling off this mortal coil. All this probably sounds ominously complicated, but it really isn't, and it will save you lots of time in the long run. Now if you could just save me some money on my life insurance premium ... C^2 Conrad Carlberg -- Excel Sales Forecasting for Dummies, Wiley, 2005 "Sabrina" wrote in message ... I am trying to set up a database for life insurance premiums. Basically, the rate is 10 times the expense constant plus 36 dollars. The expense constant is different for each age. The formula I am typing in looks like this: =(10*10.65)+36. The 10.65 in this example is the expense constant. I am trying to set up a table in which the basic formula (10*___)+36 is already entered, and when I click on a cell all I need to type in is the expense constant. When I use the format painter, I am still having to go into each formula, highlight the expense constant, and clear it out. Is this possible? |
#3
|
|||
|
|||
You may want to put your expense constant in the first column, then reference it in your calculation. For example, you may have A1=10.65 and B1=(10*A1)+36. Getting somewhat more complex, you may have different constants for different ages, as you mentioned, stored in another table. You could then use a lookup formula to find the age in the table and return the associated constant. For example you could have the age in A1 of some sheet, and have a table with constants in a sheet called Constants (formatted with the age in first column and constant in second and say 50 rows of data), then the formula in B1 of the sheet may look like =(10*(vlookup(A1,'Constant'!A1:B50,2,false))+36 ---------------------------------------------------------- I am trying to set up a database for life insurance premiums. Basically, the rate is 10 times the expense constant plus 36 dollars. The expense constant is different for each age. The formula I am typing in looks like this: =(10*10.65)+36. The 10.65 in this example is the expense constant. I am trying to set up a table in which the basic formula (10*___)+36 is already entered, and when I click on a cell all I need to type in is the expense constant. When I use the format painter, I am still having to go into each formula, highlight the expense constant, and clear it out. Is this possible? -- tobriant ------------------------------------------------------------------------ tobriant's Profile: http://www.excelforum.com/member.php...o&userid=25155 View this thread: http://www.excelforum.com/showthread...hreadid=469222 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why does Excel show a formula in ONE cell ? | Excel Discussion (Misc queries) | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
Repeating formula in Excel | Excel Discussion (Misc queries) |