Excel 2010 nested IF AND statment

Question

I have tried to write a statement for the following function,

a question and answer sheet is converting text answers into numbers Yes = 1 No = 4 Maybe = 2 and various other qustions but converting to numbers 1,2 and 4 only

Answer to Question 1 into B1 Question 2 into B2 and Question 3 into B3

I total the section in Cell B4 and populate the cell with a statement depending on the answers some example answers below

IF B1=1,B2=1,B3=1 then it will state "TRUE CONTINUE"
IF B1=4,B2=4,B3=4 then it will state "FALSE STOP"
IF B1=2,B2=1,B3=1 then it will state "TRUE CONTINUE"

SO IN CELL B4 I HAVE THIS STATEMENT

=IF((AND(B1<=3,B2<=3,B3<=3)),"TRUE CONTINUE","FALSE STOP")

Before anyone types any information Cell B4 has the text "FALSE STOP" because I have used the false statement to fill the cell with text instead of leaving it blank.

how can I write the code which leaves the cell B4 blank until all the questions have been answered in Cells B1,B3

I have tried various nested statements using if , and , Or but the code stops working or partially works

thanks Ian

Forward Ed · Accepted Answer · 2016-03-23 16:14:49Z

1

try this (assuming your if statement is doing what its supposed when all answers are entered)

=if(or(B1="",B2="",B3=""),"",IF((AND(B1<=3,B2<=3,B3<=3)),"TRUE CONTINUE","FALSE STOP"))

basically you are saying if any of the cells b1 b2 or b3 is empty/blank display blank. As long as none of the cells are empty do my if statement check.

you can get a little more convoluted and thorough by using a statement like trim(b1) or clean(b1) to ensure someone does not put a space in the cell. a space would make the cell look empty, but from excels point of view its equal to " " instead of "".

On a semi related side note. For data entry cells I usually use conditional formatting and put something like =isblank(B1) as my condition and if its true colour the background of the cell a light shade of yellow or something. As soon as somone makes an entry even if its a space, the cell background will return to normal. Just one way to grab your eye as to what might be going on in cells or highlighting where info needs to go.

edited Mar 23, 2016 at 16:14

answered Mar 23, 2016 at 16:09

Forward Ed

9,9843 gold badges29 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ian Over a year ago

This works great , I understand what I was not doing now, good tip for Conditional formatting thanks, in this case I have used a drop down box in the data entry cells which blocks spaces etc. Thanks for your help

Collectives™ on Stack Overflow

Excel 2010 nested IF AND statment

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related