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.