# Nested If statement, checking multiple cells for Blank cells

1. ## Nested If statement, checking multiple cells for Blank cells

TooManyArguments.JPG

I have tried creating a formula testing to see if three cells are not empty. If they are not empty I want a value of "1" to be returned, if not then I want to test to see if the three cells are all blank. If all three cells are blank then I want a value of "*" returned. And all else should return "0".

Excel refused my formula saying there were too many arguments. Is there a better way to write the formula or a better set of functions?

2. ## Re: Nested If statement, checking multiple cells for Blank cells

``Please Login or Register  to view this content.``
Cheers,

Post deleted

4. ## Re: Nested If statement, checking multiple cells for Blank cells

``Please Login or Register  to view this content.``

5. ## Re: Nested If statement, checking multiple cells for Blank cells

The Choose formula above isn't computing what I want when I plug the formula into excel. The formula returns * for all cases.
If all the cells are not empty I want a 1
If all the cells are empty I want *
Else 0

Interesting idea though, not very familiar with the choose function. Will try to mess with it some more.

6. ## Re: Nested If statement, checking multiple cells for Blank cells

You want the following...

1. If cells are not empty, enter 1
2. if cells are blank, enter 8
3. otherwise enter 1

How do you define the difference between empty and blank?

=IF(COUNTA(AF4:AH4)=0,"*",1)

7. ## Re: Nested If statement, checking multiple cells for Blank cells

I was using the ISBLANK function to determine if the cell was blank or not, the reason why the formula was long was because I am testing three cells and am using the NOT function, ISBLANK function, and IF

8. ## Re: Nested If statement, checking multiple cells for Blank cells

Originally Posted by JLeague
I was using the ISBLANK function to determine if the cell was blank or not, the reason why the formula was long was because I am testing three cells and am using the NOT function, ISBLANK function, and IF
That doesnt really answer my question. You are you want a certain answer if the cell is blank, and you are a different answer if the cell is empty. how do YOU define those 2 - what is the difference?

9. ## Re: Nested If statement, checking multiple cells for Blank cells

IF(AND(NOT(ISBLANK(Raw!\$AF4)),NOT(ISBLANK(Raw!\$AG4)),NOT(ISBLANK(Raw!\$AH4))),"1",IF(AND(ISBLANK(Raw!\$AF4),ISBLANK(Raw!AG4),ISBLANK(Raw!\$AH4)),"*"),"0")

Overall 2 tests then a catch all
I have three cells (AF, AG, and AH)
I tried Defining the If as if all three are not blank then assign the value of 1
Then testing If all three are empty assign the value of *
and then catch all value of 0

so overall if all three are not empty then give it a 1
if all three are empty give it a *
Else give it a 0

10. ## Re: Nested If statement, checking multiple cells for Blank cells

ok lets try again. Forget about the formula for a minute, you say the cell may be blank, or it may be empty. What is the difference between blank and empty?

Normally an empty cell is a cell that has absolutely nothing in it, while a blank cell, could contain a formula that returns "nothing" ie ""

Did you try my initial suggested formula?
=IF(COUNTA(AF4:AH4)=0,"*",1)

If those 3 cells contain absolutely nothing, that formula will return *
If any of those cells contain anything at all (even a formula that returns "") it will return 1

There are currently 1 users browsing this thread. (0 members and 1 guests)