Excel - IF Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc