can't figure this out...

[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!

 

[Norman Harker]

Hi Kimberlie!

It would help if you give an example of how you have calculated the
clients' ages.

 

[Jason Morin]

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

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!

 

[Don Guillett]

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)

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!

 

[Guest]

=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

 

[Peo Sjoblom]

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

=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.

 

[Guest]

THANK YOU all SO much!!!!!! The VLOOKUP worked great.