if and function problem

L

LarryR

New member
Joined
Jun 27, 2019
Messages
1
Reaction score
0
Points
0
Excel Version(s)
365
I have a formula question for an aged care spreadsheet: based on 3 criteria (1. Marital status? 2. are both (if a couple) in aged care?, 3. do they own their own home?)

Row 9 has Single in col b Single in col c Couple in D Couple in E Illness Separated Couple in F Illness Separated Couple in G
Row 10 has Yes in B, No in C, Yes in D, No in E, Yes in F, and No in G.
Row 11 has Yes in each column from B thru F. My formula caters for non-homeowners as you will see.
I have the following formula in cell b13
=IF(B11="no",0,(IF(AND(B9 ="single",AND(B10="Yes")),21,IF(AND(B9="Single",AND(B10="No")),22,IF(AND(B9 ="Couple",AND(B10="Yes")),31,IF(AND(B9="Couple",AND(B10="No")),32,IF(AND(B9 ="Illness Separated Couple",AND(B10="Yes")),41,IF(AND(B9="Illness Separated Couple",AND(B10="No")),42))))))))

When I drag the formula across row 13, from row b thru to G, I get the following values.

21 FALSE 31 32 41 42

Why do I get "FALSE" in c13 when the other answers are numerical and seem correct?

 
Hi,

Make sure that you don't have a leading or trailing space at the beginning of 'Single', 'No' or 'Yes' for the column C

I copied your formula and it worked first time.
 
I echo jono's comment, but try this instead too, if you have a recent enough version of Excel:
Code: 
=IF(C11="no",0,SWITCH(C9,"single",20,"couple",30,"Illness Separated Couple",40,NA())+SWITCH(C10,"yes",1,"no",2,NA()))
 
Last edited:
You must log in or register to reply here.
Back
Top