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?

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?

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