**Question description:**

*This user has given permission to use the problem statement for this blog.*

hi,
No matter what argument I put in the If statement the result is zero Ihave truied everyting possible, I want to set up an argument for if a cell is blank thenblank
so I sais if(AB3="","blank","noblank")

Solved by B. B. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

User
29/03/2018 - 06:47

HI

Excelchat Expert
29/03/2018 - 06:47

hi there!

Excelchat Expert
29/03/2018 - 06:47

I am sure I can help you out...

User
29/03/2018 - 06:48

did you read my problem?

User
29/03/2018 - 06:48

thanks

Excelchat Expert
29/03/2018 - 06:48

so if the cell is blank, then you want the return to say "blank", and if it's not blank, you want it to return "noblank"?

Excelchat Expert
29/03/2018 - 06:49

check that out... I used ISBLANK within an IF statement

User
29/03/2018 - 06:50

there are three columns in my data , if all 3 are blank i want the entire observation to be deleted, to achiev ethat i was gona code this that if IFthe cell is blank return blanlk.

Excelchat Expert
29/03/2018 - 06:50

so you could replicate that 3 times for each of your columns

User
29/03/2018 - 06:50

yup

Excelchat Expert
29/03/2018 - 06:50

cool!

User
29/03/2018 - 06:50

I tried wht u did and it didnt work it returns a zero

User
29/03/2018 - 06:51

I tried ISBLANK before i tried only if

Excelchat Expert
29/03/2018 - 06:51

hmmm that is odd... are you able to share your file? Also, when you tried ISBLANK, did you use it within an IF?

User
29/03/2018 - 06:52

sorry i cant share my file. has confifdnetial info

Excelchat Expert
29/03/2018 - 06:52

totally understand

User
29/03/2018 - 06:52

can this happen if the file is protected?

User
29/03/2018 - 06:52

because the other cells i tried it there as well but it didnt work

Excelchat Expert
29/03/2018 - 06:52

does it return 0 regardless if the reference cell is blank or not?

Excelchat Expert
29/03/2018 - 06:53

i don't think the file protection would effect the formula functionality

User
29/03/2018 - 06:53

YES i JSUT TRIED AND THAT HAPPENED

User
29/03/2018 - 06:54

sorry caps was on

Excelchat Expert
29/03/2018 - 06:54

its all good

Excelchat Expert
29/03/2018 - 06:54

so it's 0 regardless of if the cell is blank or not?

User
29/03/2018 - 06:54

yup I tried to use IF in a cell which was not blank and to return true if it ontaine dthe value it still returend 0

Excelchat Expert
29/03/2018 - 06:56

ok... so I can visualize... you have a column of data. some cells in that column (lets say column A) are blank. Empty. Others have values in them. is that correct?

User
29/03/2018 - 06:56

yes

Excelchat Expert
29/03/2018 - 06:56

yeah create it in the google sheet for me

Excelchat Expert
29/03/2018 - 06:56

good idea

User
29/03/2018 - 06:57

ifduno how to eneter function here

User
29/03/2018 - 06:57

it doesnt work

User
29/03/2018 - 06:57

but any way e2

User
29/03/2018 - 06:58

for E2

User
29/03/2018 - 06:58

i wana enter if (e2="","blank",noblank)

User
29/03/2018 - 06:58

and it doenst work

Excelchat Expert
29/03/2018 - 06:59

so the cell you're referencing (checking to see if it is blank) and the cell you're entering the formula in are the same?

Excelchat Expert
29/03/2018 - 06:59

for E2

Excelchat Expert
29/03/2018 - 06:59

i wana enter if (e2="","blank",noblank)

User
29/03/2018 - 06:59

yes I know i thot the same thing circular reference but does it matter?

Excelchat Expert
29/03/2018 - 06:59

100%!

User
29/03/2018 - 06:59

yes.

User
29/03/2018 - 06:59

so what shpould i sdo

User
29/03/2018 - 06:59

should i do?

Excelchat Expert
29/03/2018 - 07:00

you need to use a different column for your formula

Excelchat Expert
29/03/2018 - 07:00

like see my example

Excelchat Expert
29/03/2018 - 07:00

column B is the data

Excelchat Expert
29/03/2018 - 07:00

column C is the formula

User
29/03/2018 - 07:01

But then i want to delete tose columns whichj have blank in them for all the three main columns

Excelchat Expert
29/03/2018 - 07:02

yes this is what I need :)

User
29/03/2018 - 07:03

see observation 2

User
29/03/2018 - 07:03

i wana de;ete that '

User
29/03/2018 - 07:03

delete that

Excelchat Expert
29/03/2018 - 07:03

so the only row you'd want to keep in that example is row 3?

User
29/03/2018 - 07:03

since it sblank in all 3

User
29/03/2018 - 07:03

no

Excelchat Expert
29/03/2018 - 07:03

oh i see.

User
29/03/2018 - 07:03

no

Excelchat Expert
29/03/2018 - 07:03

would you also want row 4 deleted then?

User
29/03/2018 - 07:03

Evenif 1 cell has any value we keep that row

Excelchat Expert
29/03/2018 - 07:03

gotcha

Excelchat Expert
29/03/2018 - 07:04

so this is a few steps...

Excelchat Expert
29/03/2018 - 07:04

but let me show you

User
29/03/2018 - 07:04

so keep 1,3 ,5

User
29/03/2018 - 07:04

okay

Excelchat Expert
29/03/2018 - 07:05

so add that formula to your sheet

Excelchat Expert
29/03/2018 - 07:06

then there is another manual step to delete the rows

Excelchat Expert
29/03/2018 - 07:06

let me know once you enter the formula and if it works

User
29/03/2018 - 07:06

I think i understood now

User
29/03/2018 - 07:06

let me try

Excelchat Expert
29/03/2018 - 07:06

take your time

User
29/03/2018 - 07:08

IT WORKED

Excelchat Expert
29/03/2018 - 07:08

Yes!!

Excelchat Expert
29/03/2018 - 07:08

you did it

User
29/03/2018 - 07:08

now tell me

Excelchat Expert
29/03/2018 - 07:08

so to delete them...

Excelchat Expert
29/03/2018 - 07:09

do you know how to add filters

User
29/03/2018 - 07:09

i have top delte the rows that have blank for all three

Excelchat Expert
29/03/2018 - 07:09

like this (google sheet window check it out)

User
29/03/2018 - 07:09

so my entire document has filters

User
29/03/2018 - 07:09

ok

Excelchat Expert
29/03/2018 - 07:09

wicked

Excelchat Expert
29/03/2018 - 07:09

so - just filter on the Check column

Excelchat Expert
29/03/2018 - 07:09

select the "Delete"

Excelchat Expert
29/03/2018 - 07:09

then, delete those entire rows

User
29/03/2018 - 07:10

question : how did you go back?

Excelchat Expert
29/03/2018 - 07:10

then re-select the "Keeps"

User
29/03/2018 - 07:10

after filteroing for delete

Excelchat Expert
29/03/2018 - 07:10

you could press Undo

User
29/03/2018 - 07:11

okay so I filter for the delete

Excelchat Expert
29/03/2018 - 07:11

yep correct

User
29/03/2018 - 07:11

then i dlete it the entire row goe?

User
29/03/2018 - 07:11

and then how do i get the rest of the keep?

User
29/03/2018 - 07:12

again?

Excelchat Expert
29/03/2018 - 07:12

left click on the row number

Excelchat Expert
29/03/2018 - 07:12

so left click on the first row number, drag all the way down until all the rows are selected

Excelchat Expert
29/03/2018 - 07:12

then RIGHT click, select delete rows

User
29/03/2018 - 07:12

show me again?

Excelchat Expert
29/03/2018 - 07:12

sure thing check this out

Excelchat Expert
29/03/2018 - 07:13

then you go abck to the filter and RESELECT "keep"

User
29/03/2018 - 07:13

awesome i am gona try

Excelchat Expert
29/03/2018 - 07:13

i can wait!

User
29/03/2018 - 07:18

i think i got it

Excelchat Expert
29/03/2018 - 07:18

that is awesome news

User
29/03/2018 - 07:18

theres no way i missed any obseravtion right?

User
29/03/2018 - 07:19

becaus when i slected all the delete to delete they said 702 records from 2588

Excelchat Expert
29/03/2018 - 07:19

well - you can do a few spot checks to make sure the check formula is working - but it's solid I'm sure you're good

Excelchat Expert
29/03/2018 - 07:19

so 702 rows had blanks in all 3 columns

User
29/03/2018 - 07:19

and when i selectyed all the keeps from filter it says only 79

Excelchat Expert
29/03/2018 - 07:19

I would have to see the sheet to confirm all of this

User
29/03/2018 - 07:19

yea but then it should give me 1866 rows riughyt?

User
29/03/2018 - 07:20

i am sorry i wish icould

Excelchat Expert
29/03/2018 - 07:20

me too!

Excelchat Expert
29/03/2018 - 07:20

yes your math is correct

Excelchat Expert
29/03/2018 - 07:20

so you only have 79 rows remaining? from 2588?

User
29/03/2018 - 07:21

wait if a record is hissen is it still a part of the count?

User
29/03/2018 - 07:21

because some of the records are hidden

Excelchat Expert
29/03/2018 - 07:21

it would be better if you unhid all rows, then pull the check formula down, then do the filter again

User
29/03/2018 - 07:21

oh it says 79 of 1886 records found

Excelchat Expert
29/03/2018 - 07:21

because yes that will affect this

Excelchat Expert
29/03/2018 - 07:22

ah yes that makes sense then

User
29/03/2018 - 07:22

how does it?

Excelchat Expert
29/03/2018 - 07:22

im guessing most of the rows are hidden

Excelchat Expert
29/03/2018 - 07:22

because the hidden rows do not have the check formula

User
29/03/2018 - 07:22

okay i am gona do what yopu said and then try

Excelchat Expert
29/03/2018 - 07:22

so, column F has NEITHER Keep OR Delete

User
29/03/2018 - 07:22

also i had another question

User
29/03/2018 - 07:23

ohh makes sense

Excelchat Expert
29/03/2018 - 07:23

im so sorry but I can only answer one question per session my friend! but what is it :)

User
29/03/2018 - 07:23

sorry

Excelchat Expert
29/03/2018 - 07:23

hey its all good

User
29/03/2018 - 07:23

you saw that

User
29/03/2018 - 07:23

series

Excelchat Expert
29/03/2018 - 07:23

if its quick im happy to help!

Excelchat Expert
29/03/2018 - 07:23

totally

User
29/03/2018 - 07:23

i want 1

User
29/03/2018 - 07:23

2

User
29/03/2018 - 07:23

2

User
29/03/2018 - 07:23

3

User
29/03/2018 - 07:24

but it repeats

User
29/03/2018 - 07:24

what should i do for it to follow

Excelchat Expert
29/03/2018 - 07:24

manually enter 1 and 2

Excelchat Expert
29/03/2018 - 07:24

then highlight both cells and THEN use the click and drag + sign

User
29/03/2018 - 07:25

i did that it copied the 1

Excelchat Expert
29/03/2018 - 07:25

watch this

User
29/03/2018 - 07:25

ok

Excelchat Expert
29/03/2018 - 07:25

ok so select all of those #s

Excelchat Expert
29/03/2018 - 07:25

then pull down the square

Excelchat Expert
29/03/2018 - 07:26

you have to select cells E11, E12 and E13

Excelchat Expert
29/03/2018 - 07:26

not just E11

Excelchat Expert
29/03/2018 - 07:26

press and hold left mouse button, drag until ALL num,bers are selected

Excelchat Expert
29/03/2018 - 07:26

THEN pull down

User
29/03/2018 - 07:26

okay i got it ithinlk

Excelchat Expert
29/03/2018 - 07:26

try it in Excel though

Excelchat Expert
29/03/2018 - 07:27

see if it works for you

User
29/03/2018 - 07:27

okay could you juts wait moment ill try it will take a sec

Excelchat Expert
29/03/2018 - 07:27

of course

User
29/03/2018 - 07:27

hey it still copies the series

User
29/03/2018 - 07:27

thats 1

Excelchat Expert
29/03/2018 - 07:28

if it copied the series, then it would show 1 2 3 3 3 3 3 3 3

Excelchat Expert
29/03/2018 - 07:28

manualyl enter the 2

User
29/03/2018 - 07:28

yes i did

Excelchat Expert
29/03/2018 - 07:29

and you left click to select both cells?

Excelchat Expert
29/03/2018 - 07:29

and then pull down

User
29/03/2018 - 07:29

yes

User
29/03/2018 - 07:29

just like i did on ur sheet and it worked

User
29/03/2018 - 07:29

it dint on mine

Excelchat Expert
29/03/2018 - 07:29

and it goes 1 2 1 1 1 1 1 1 1 1?

User
29/03/2018 - 07:29

i nope 111111

User
29/03/2018 - 07:29

after slecting both cells

Excelchat Expert
29/03/2018 - 07:30

is it overwriting the 2 you manually entered?

User
29/03/2018 - 07:30

yup

Excelchat Expert
29/03/2018 - 07:30

you need to pull down from the second cell

Excelchat Expert
29/03/2018 - 07:30

not the first one

Excelchat Expert
29/03/2018 - 07:30

this is whats happening ->

User
29/03/2018 - 07:30

after slectimng both ce;lls ilpulkl from the cell i enmtered 2 in right?

User
29/03/2018 - 07:30

id id that

User
29/03/2018 - 07:31

you see i did that on your sheet

User
29/03/2018 - 07:31

on mine it doesnt work

Excelchat Expert
29/03/2018 - 07:31

ok at the bottom number 1

Excelchat Expert
29/03/2018 - 07:31

aftyer you pull down

Excelchat Expert
29/03/2018 - 07:31

there should be a little icon when you hover your mouse over the bottom of the last cell with a 1 in it

Excelchat Expert
29/03/2018 - 07:31

click it

Excelchat Expert
29/03/2018 - 07:31

and select FILL SERIES

User
29/03/2018 - 07:32

Nope that happens when i work on my mac usually but here its not happeneing

User
29/03/2018 - 07:32

thats why i am confused

Excelchat Expert
29/03/2018 - 07:32

no idea then my friend

User
29/03/2018 - 07:32

Okay well thanks for your help so far

Excelchat Expert
29/03/2018 - 07:32

hey

Excelchat Expert
29/03/2018 - 07:32

is the file the same as the confidential one?

User
29/03/2018 - 07:33

yep

Excelchat Expert
29/03/2018 - 07:33

darn

Excelchat Expert
29/03/2018 - 07:33

wanted to replicate this because it's super weird.

User
29/03/2018 - 07:33

i nknow man

User
29/03/2018 - 07:33

i have worked these cells on excel befoe and never jhasd a problen

User
29/03/2018 - 07:34

i duno whats with thi sfile

Excelchat Expert
29/03/2018 - 07:34

that icon thing i said - did you try that?>

User
29/03/2018 - 07:34

that the thing no icon turns up

User
29/03/2018 - 07:35

i even checked in th settings

User
29/03/2018 - 07:35

no problen ther

Excelchat Expert
29/03/2018 - 07:36

what about this setting: Click the microsoft office button (or "FILE"), and then click Options. Click Advanced, and then under Editing options, select the Enable Fill handle and cell drag-and-drop check box to hide or show the fill handle.

Excelchat Expert
29/03/2018 - 07:36

i am hoping it was UNSELECTED

Excelchat Expert
29/03/2018 - 07:37

select the option, then try dragging it down again (with the 1 and 2 cells selected and dragging from the 2 cell)

Excelchat Expert
29/03/2018 - 07:44

did that work? I am so curious :)

User
29/03/2018 - 07:45

Lol sorry it already checked

Excelchat Expert
29/03/2018 - 07:45

ah!

User
29/03/2018 - 07:45

i know me too

User
29/03/2018 - 07:45

i a so curious

User
29/03/2018 - 07:45

well any other oway ? should i just use formula ?

User
29/03/2018 - 07:45

like count+1

Excelchat Expert
29/03/2018 - 07:45

smart idea

Excelchat Expert
29/03/2018 - 07:46

i'd just do this ->

Excelchat Expert
29/03/2018 - 07:46

cell G12

Excelchat Expert
29/03/2018 - 07:46

then drag that formula

User
29/03/2018 - 07:46

Okay well

User
29/03/2018 - 07:46

thanks

Excelchat Expert
29/03/2018 - 07:47

no problem!

User
29/03/2018 - 07:47

ill do that '

User
29/03/2018 - 07:47

:)

User
29/03/2018 - 07:47

good day

Excelchat Expert
29/03/2018 - 07:47

perfect

Excelchat Expert
29/03/2018 - 07:47

you also!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*