can't figure this out...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have ages calculated in a rather large worksheet. I need to pull rates based on those ages. I tried the "IF" formula, but it will not work. I have 9 different prices I need to pull from depending on what the client's age is. Can anyone tell me how to make this work???? Please and thank you!
 
Hi Kimberlie!

It would help if you give an example of how you have calculated the
clients' ages.
 
Typically when you find yourself trying to use several IF
nested IF statements, it's a good idea to try a lookup
table. MVP John Walkenbach has an example of using VLOOKUP
here:

http://j-walk.com/ss/excel/usertips/tip080.htm

HTH
Jason
Atlanta, GA
-----Original Message-----
I have ages calculated in a rather large worksheet. I
Click to expand...
need to pull rates based on those ages. I tried the "IF"
formula, but it will not work. I have 9 different prices
I need to pull from depending on what the client's age
is. Can anyone tell me how to make this work???? Please
and thank you!
 
1st. You will get more answers with a meaningful subject line.
2nd. Try HELP index for LOOKUP or VLOOKUP and then make a table
=vlookup(age,lookuptable,2,0)

--
Don Guillett
SalesAid Software
(e-mail address removed)
Kimberlie said:
I have ages calculated in a rather large worksheet. I need to pull rates
Click to expand...
based on those ages. I tried the "IF" formula, but it will not work. I
have 9 different prices I need to pull from depending on what the client's
age is. Can anyone tell me how to make this work???? Please and thank you!
 
=DATEDIF(G7,$C$4,"y") is the formula I used to calcuate the age. This is referencing a column with a birthdate (G7) and a cell ($C$4) with a proposed effective date for policies. Now I need to reference a range of prices based on the age that is calculated. There are nine prices (age banded) to choose from, and I am stumped as to how to make it work. I appreciate any guidance you can offer

----- Norman Harker wrote: ----

Hi Kimberlie

It would help if you give an example of how you have calculated the
clients' ages
 
Use a vlookup table, but the lower value of each age in a column in
ascending order like

0 200
15 250
25 300
etc


then use

=VLOOKUP(lookup_age,A2:B10,2)

now if you use another cell to put the age in

=VLOOKUP(D1,A2:B10,2)

using my example if you put in 10 in D1 the value that's returned is 200,
between 15 and 24 is 250 and so on
--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Kimberlie said:
=DATEDIF(G7,$C$4,"y") is the formula I used to calcuate the age. This is
Click to expand...
referencing a column with a birthdate (G7) and a cell ($C$4) with a proposed
effective date for policies. Now I need to reference a range of prices
based on the age that is calculated. There are nine prices (age banded) to
choose from, and I am stumped as to how to make it work. I appreciate any
guidance you can offer.
 
Back
Top