Nested IF AND formulas in one cell

Microsoft office 2007
September 4, 2010 at 10:58:56
Specs: Windows 7 Home 64bit, 3gb
I am working up a membership' database' and would like to input the amount due. B2 has a list containing different types of membership (Adult Indiv, Youth Indiv, Combo, etc all being a different fee). C2=1 year membership, D2=3 year membership. In F2, I would to calculate IF B2=Adult and C2>0 (meaning there is an 'x' in the cell) return $15 but then I also need it to calculate IF B2=Adult and D2>0, return $35 And the same formulas for the rest of the membership types I'm having a hard time wrapping my head around the formula setup and after much searching, decided that I would ask. Thanks in advance

See More: Nested IF AND formulas in one cell

Report •

September 4, 2010 at 11:55:08
Found a similar formula that helped explain a little better for me. This is the formula I came up with-only, how do I close it out? 1 ) or more, or some other? =IF(AND(B2=”Adult”,C2=”x”),$15,(IF(AND(B2=”Adult”,D2=”x”),$30,(IF(AND(B2=”Youth”,C2=”x”),$13,(IF(AND(B2=”youth”,D2=”x”),$25,(IF(AND(B2=”Combination”,C2=”x”),$17,(IF(AND(B2=”Combination”,D2=”x”),$35,(IF(AND(B2=”Family”,C2=”x”),$19,(IF(AND(B2=”Family”,D2=”x”),$40

Report •

September 4, 2010 at 12:15:09

I would use a table approach.

Make a table in cells K1 to M4 (you will make it larger to accommodate more membership options)
It looks like this:

	K		L	M
1			1 year	3 year
2	Adult Indiv	15	40
3	Youth Indiv	10	25
4	Combo		30	80

In cell B2 you use a data validation drop-down to select membership type (you can re-use the table descriptions in cell K2 to K4 as the source of the validation list.
In cell C2 again use a data validation drop-down to select 1 year or 3 year membership.

In cell D2 enter this formula:
=VLOOKUP(B2,$K$2:$M$4,IF(C2="1 year",2,3),FALSE)

You can copy cells B2 to D2 and paste them to subsequent rows if you need this calculation for each member.

The formula works by using the vertical lookup function, VLOOKUP(), that searches for a value, in this case, the membership type, in a column of cells (K2 to K4) and returns a value from the same row, but in a column to its right.
The column is selected by the IF function which returns the second column if the value in C2 is "1 year" or if not, it returns the third column.
(The value FALSE in the VLOOKUP function ensures that there is an exact match for the membership type even if the types are not in alphabetical order).

As to your original problem:
Using x in C2 to mean 1 year and x in D2 to mean 3 year membership:
a. You could end up with x in both - so there is a problem there.
b. Your test "If C2 >0" is not very specific. Generally an empty cell is tested by If C2 <> ""
i.e. that there is nothing in the cell.
c. You have to create a formula that can test for several membership types in combination with either C2 containing x or D2 containing x
This gets quite complex - here it is for just three types and using the same costs as shown in the table:
=IF(OR(AND(C2<>"",D2<>""),AND(C2="",D2="")),"Error",IF(B2="Adult Indiv",IF(C2<>"",15,40),IF(B2=" Youth Indiv",IF(C2<>"",10,25),IF(B2="Combo",IF(C2<>"",30,80)))))

The first part of the formula:
is just doing some basic tests on the two membership period cells C2 and D2.
It tests that they are not both empty or that both contain data. In either case the result of the If test is true, and If returns the value "Error".
Note that there is no test that one or other cell contains "x", one or other could contain "z"!
The rest of the formula consists of a further three 'nested' If functions, each with a single nested If within it.
Each one is essentially the same:
IF(B2="Adult Indiv",IF(C2<>"",15,40)
This uses IF to test if B2 contains "Adult Indiv", if it doesn't, the result is false and the next If function testing for "Youth Indiv" will be used, and if that is False, then the next If function testing for "Combo" will be used ... more If's can be nested to test for more membership types.
In the If that tests for "Adult Indiv", If B2 contains "Adult Indiv", the If test is true and this is used: IF(C2<>"",15,40)
This tests cell C2. If C2 is not empty, then the 1 year amount is returned. If it is empty then the 3 year amount is returned (as we tested and excluded both C2 and D2 being empty, then when C2 is empty we can assume that D2 is not empty).

If you used this formula on many rows, any change in membership fees would have to be made to every formula. If you added or removed a membership type, all the formulas would have to be rewritten - think of all those parentheses))))))))))))))))))))))

So a table/lookup approach is typically easier to manage.

Hope this was clearer than mud.


Report •

September 4, 2010 at 14:36:41
Holy Crap! That's awesome! Guess I'll have to look into tables-never really knew what that was for and what it could do! After doing some thinking, I wondered if I shouldn't be using the VLOOKUP.
Question: The table acts like a list sort of, right? If so, then this doesn't need to be visible to me..only if dues change. Just move it out of the way or send it to another sheet? (This worksheet has MANY columns being used for info.

Thanks for the HUGE help! My throbbing head thanks you!

Report •

Related Solutions

September 5, 2010 at 05:01:31

You're right - the table can be out of sight. You can use columns way over to the right, or more practical, create a new worksheet in the same workbook, to contain your table of rates.

In Excel 2007 and later you can have the lists for cell data validation (cell drop-down lists) on different worksheets. In Excel 2003 and earlier the list has to be on the same worksheet (unless you use named ranges).

The only downside to a table approach is that everything changes when you update the table. This means that dues already paid will change.
I would consider a table for each year, as outlined below:

Make two tables - one for the 2010 year dues and one for the 2011 year dues.
Because we are going to use 'named' ranges it doesn't matter what worksheet, or what rows/columns are used.

Discount		5.0	10.0
Period		1 years	2 years	3 years
Adult Indiv	15.00	29.25	28.50
Youth Indiv	10.00	19.50	19.00
Combo		30.00	58.50	57.00
Discount		5.0	10.0
Period		1 years	2 years	3 years
Adult Indiv	16.00	31.20	30.40
Youth Indiv	11.00	21.45	20.90
Combo		31.00	60.45	58.90

Select the cells in the first (2010) table from "Adult Indiv" to 57.00
In the cell address area - the rectangle just above the column letters A & B and to the left of the formula bar, enter "Rates2009"
Don't include the double quotes and make sure you click 'Enter' before you move the cursor out of the address area. The name does not have a space in it.
Repeat for 2011 and name it "Rates2011". These ranges of cells can now be referred to by name rather than by traditional letters/numbers. In this example Rates2010 is the same as K11:N13. If you needed to move the range of cells K11:N13, the name would stay the same - even if you inserted cells into the range, to add an additional membership type, the name would stay the same.

Now select the cells in the 2010 table from "Period" to 3 years and name it "Periods2010"
Repeat for 2011 - "Periods2011"

I included a discount calculation for periods more than one year
If the Adult Indiv rate for 1 year in 2010 is in cell L11, then the discount rate for 2 years will be in cell M9
Enter the three base rates (1 year rates) in cells L11, L12 & L13.
In cell M11 enter this formula:=($L11*2)-($L11/100*M$3)
Note the position of $ signs. They stop parts of cell addresses changing as formulas are dragged to extend them. Drag the formula to extend it down to M13, then select M11 to M13 and drag the three cells to extend them to column N.

Now back to the main worksheet.
In cells B1, to E1 enter this:

	B	C	D	E
1	Type	Period	Base	Due

In cell B2 create a drop-down validation list from the membership types in the table.
By using the table you are sure that when VLOOKUP looks for a membership type it will find it, so we don't need to add error checks around VLOOKUP.
Do the same for C2 - you can use a horizontal range of cells for the periods list.
For D2 use a data validation list - you will need to create the list for it to use 2010, 2011.

Now we have all the elements needed to create the formula in E2 which will produce an amount due based on the three factors - membership type, subscription period and the base year for the rate calculation.
Enter this in D2:
=VLOOKUP(B2,INDIRECT("Rates" & D2),MATCH(C2,INDIRECT("Periods" & D2),0),FALSE)

The formula uses INDIRECT to convert text to an address. In this case the addresses created are the named ranges - one for VLOOKUP to use and the other for MATCH to use.
MATCH finds a value, in this case in the row that starts "Period", and finds the position of the period, e.g., "2 years" and returns a number which is the offset from the start of the list. "2 years" will give a value of 3, and this value is used as the column offset in the VLOOKUP function.

Hope this helps.


Report •

September 6, 2010 at 11:43:42
I'm still working on wrapping all of this around my head!! Thanks for the response and the great help!

Report •

Ask Question