[SOLVED} If function with 4 conditions

**andy9988** · 03-15-2010, 01:30 PM

Dear All,

Can any one help in If Function. I m creating a "Rate Calculator"

Conditions:

Input Data: Entering DOB in 6 different Cells.

Now conditions are : Find no of parents from those 6 cells. That would be either 2 (1 Father & 1 Mother) or 1(only Father or only mother). For Eg.If there is no Father in family his age would be "0" in cell reflecting his age. Same case is for mother

find no of children from remaining 4 cells. (Max it can be 4)

The above data is entered manually by user.

Now what I require is count the Premium for family for different sum insured. Say like combination of 2 Parents will have different premium and combination of 1 parent and 1 child will have different premium.

The premium rate depends on the age of the person having highest age.

**darkyam** · 03-15-2010, 04:16 PM

Your tables are incomplete. They don't have rates for adults 25 or under and you don't have tables for either just one adult or for either one or two adults with four children. Also, I would recommend you keep an even number of blank rows between your tables, which would enable us to make some sort of Index/Match formula to look up the data.

**andy9988** · 03-16-2010, 12:21 PM

Ok. I have revised the table.
There will never come a scenario when there is only one adult so if some one enters only one DOB from the first two cells (these cells are for the age of parents only) and other remaining other 4 are also blank, the answer should come "0". So there has to be at least two DOB. They can be 2 adults(Parents) and other combination that are shown in table.

Now if there are 6 members in family i.e. 2 adults and 4 children then also result should come "0".

So if conditions are satisfied according to tables then the answer from table and if not satisfied then answer is "0". Likewise if the maximum age is more than 45 then also it will reflect "0".

I had used Vlook up for another, but cant write function when there are so many conditions are there.

**darkyam** · 03-16-2010, 04:11 PM

I took the liberty of editing your file further. I cut out the merge & center cells (merging almost always causes formula or other problems if you're doing anything advanced) and put a formula in the upper left cell of each table that shows the number of adults and children easily, such as 2,1. It won't be capable of producing all the different values you have and the formula assumes both the same age breaks for all tables and the same dollar breaks. I tested it lightly and it seems to work alright. Let me know if you have issues with it.

**andy9988** · 03-17-2010, 01:22 PM

Thanx. It Works gr8, But there is one problem, it gives the same result even if the maximum age is above 46. It should come "0". It shows the rate from 36-45 Range. and if we enter DOB in all 6 cells then it shows N/A, but then also it should be "0".

Please tell me the meaning of following so that in future i can also create functions.

1) A:A or D:D, Does it show the range ?????
2) "," ?????
3) What is the logic if +6 , +3, +1. ???? I am not getting it..
4) What was the logic of changing the age Band in Sheet "A". I dont have any problem but i want to know the logic behind it.

**darkyam** · 03-17-2010, 01:32 PM

Change the formula to =IF($C$21>=46,0,current formula)
1. Index looks at an array and returns the value of a cell a certain number of rows down and possibly a certain number of cells across. A:A and D:D refer to the entirety of those columns.
2. I had to use a comma in there because that is how I did it with the cells at the top left of the table. They are formatted as in 2,1 for two adults and one child, so the formula has to match 2,1 to that, thus the comma has to be put in between the numbers.
3. The first Index function finds the upper left corner of each table. The real data in the tables start three columns down and end six columns down. Essentially, I'm using multiple Index/Match combos to get the proper range for the table, then one more to get the actual value returned from that table.
4. I had to change the age because <25, 26-35, and 36-45 would be recognized as text and it would have been more of a pain to get Excel to match them up to the max age. My way just simplified that process by making them each one number, which can then, of course, be read as a number.

**andy9988** · 03-18-2010, 12:58 PM

Thax,. But still 2 +4 gives the result of #N/A.

Still there are doubts

2. I had to use a comma in there because that is how I did it with the cells at the top left of the table. They are formatted as in 2,1 for two adults and one child, so the formula has to match 2,1 to that, thus the comma has to be put in between the numbers. ??????? I m not getting

I had asked whts "," ? Y its used ???

In my tables there were <25, 26-35, and 36-45 but in your tables. the starting point has been taken, but then how come it finds the value which is less than 26, coz we have mentioned 18 over there.

whts this =====> =LEFT(B2)&","&IF(ISNUMBER(FIND("+",B2)),MID(B2,FIND("+",B2)+2,1),0) ????

Is it the way to find no of adults and children in family ??

**darkyam** · 03-18-2010, 02:51 PM

Change the first if condition to IF(OR($C$21>=46,$C$19>=4),0,current formula)
The "," is a comma inserted. I don't really know how to explain it clearer. If I just had the numbers, say 2&1 to make 21, it'd be easy for someone else reading it to have no idea what it's there for. Match, when looking up text values like "2,1" needs an exact match, so it can't be looking up "2,1" against "2 adults, 1 child" and find anything.

For the 18 question, 18 is there because it's the legal age to sign a contract, meaning one is an adult. If it's under 18, then it's a child and wouldn't appear as an adult anyway from your Input sheet. From 18-25 falls in that bracket, just like it would have before with it being <25.

For your last question, yes, that is correct.