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

#1
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 •

#2 September 4, 2010 at 12:15:09
 Hi,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 xThis 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:IF(OR(AND(C2<>"",D2<>""),AND(C2="",D2="")),"Error"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.Regards

Report •

#3
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

#4 September 5, 2010 at 05:01:31 