Multiple IF function
in [Functions]
|
Prev: On Time Calculation
Next: Highlight cells
From: Anthony on 13 Mar 2010 08:09 Hi, I'm trying to create a Life Insurance Calculator within Excel. I've already done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able to reference back to them given the option. The four options available are: 1. Male Non-Smoker 2. Male Smoker 3. Female Non-Smoker 4. Female Smoker For example: 1. If Male AND a Non-Smoker, I want to refer to Cell A1 2. If Male AND a Smoker, I want to refer to Cell A2 3. If Female AND a Non-Smoker, I want to refer to Cell A3 4. If Female AND a Smoker, I want to refer to Cell A4 Any help is much appreciated. Thanks
From: Don Guillett on 13 Mar 2010 08:52 If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. You send a clear explanation of what you want 4. You send before/after examples and expected results. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Anthony" <Anthony(a)discussions.microsoft.com> wrote in message news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com... > Hi, > > I'm trying to create a Life Insurance Calculator within Excel. I've > already > done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able > to > reference back to them given the option. The four options available are: > 1. Male Non-Smoker > 2. Male Smoker > 3. Female Non-Smoker > 4. Female Smoker > > For example: > 1. If Male AND a Non-Smoker, I want to refer to Cell A1 > 2. If Male AND a Smoker, I want to refer to Cell A2 > 3. If Female AND a Non-Smoker, I want to refer to Cell A3 > 4. If Female AND a Smoker, I want to refer to Cell A4 > > Any help is much appreciated. > > Thanks >
From: Bob Phillips on 13 Mar 2010 09:20 How about =INDEX(A1:A4,MATCH(selected_option,option_cells,0)) -- HTH Bob "Anthony" <Anthony(a)discussions.microsoft.com> wrote in message news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com... > Hi, > > I'm trying to create a Life Insurance Calculator within Excel. I've > already > done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able > to > reference back to them given the option. The four options available are: > 1. Male Non-Smoker > 2. Male Smoker > 3. Female Non-Smoker > 4. Female Smoker > > For example: > 1. If Male AND a Non-Smoker, I want to refer to Cell A1 > 2. If Male AND a Smoker, I want to refer to Cell A2 > 3. If Female AND a Non-Smoker, I want to refer to Cell A3 > 4. If Female AND a Smoker, I want to refer to Cell A4 > > Any help is much appreciated. > > Thanks >
From: Don Guillett on 13 Mar 2010 09:26 One way using a data validation restricted cell (g14) with msn,ms,fns,fs =INDIRECT("a"&LOOKUP(G14,{"fns","fs","mns","ms";3,4,1,2})) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Anthony" <Anthony(a)discussions.microsoft.com> wrote in message news:B6C75434-45F7-4C5F-9430-6155E43A0AEA(a)microsoft.com... > Hi, > > I'm trying to create a Life Insurance Calculator within Excel. I've > already > done a VLOOKUP to get the Relevant Premium Rate, but now I need to be able > to > reference back to them given the option. The four options available are: > 1. Male Non-Smoker > 2. Male Smoker > 3. Female Non-Smoker > 4. Female Smoker > > For example: > 1. If Male AND a Non-Smoker, I want to refer to Cell A1 > 2. If Male AND a Smoker, I want to refer to Cell A2 > 3. If Female AND a Non-Smoker, I want to refer to Cell A3 > 4. If Female AND a Smoker, I want to refer to Cell A4 > > Any help is much appreciated. > > Thanks >
From: Anthony on 13 Mar 2010 10:21
Thanks guys, I've spent the last few hours working on this and I figured it out! My formula as follows: =IF($B$3="Male", IF($B$4="Non-Smoker", 'Premium Rate Table'!L4, 'Premium Rate Table'!L5), IF($B$3="Female", IF($B$4="Non-Smoker",'Premium Rate Table'!L6,'Premium Rate Table'!L7))) B3 = Drop down list where you can choose "Male" or "Female" B4 = Drop down lise where you can choose "Non-Smoker" or "Smoker" 'Premium Rate Table' = Separate Worksheet with the Premium Rate Tables. L4 = Male Non-Smoker Premium Rate L5 = Male Smoker Premium Rate L6 = Female Non-Smoker Premium Rate L7 = Female Smoker Premium Rate The L# fields already have a VLOOKUP formula to obtain the correct Premium Rate when the relevant Age is entered. Thanks for your responses anyway. Anthony |