Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a Google Sheet with several formulas like this one in cell AN5. =IF(AND($H5 = "VendorName",$D$1 >= AN$1),AM5,) $D$1 is the current month and AN$1 is the month number for that column. That is, $D$1 is 8 and $AN$1 is 6 (June). The cell should contain the amount from cell AM5 if H5 is "VendorName" and if the month in AN$1 is less than 8. The cell should be blank otherwise. When exported to Excel, the formula result is 0 and not blank. This changes how other cells that reference THIS cell works, making it impossible to use the exported file. It isn't possible to copy and export all 32 tabs as values. Any other suggestions?
Solved by O. H. in 47 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 07/08/2018 - 04:40
Hi
User 07/08/2018 - 04:40
Hi!
User 07/08/2018 - 04:41
I hope the explanation was clear.
Excelchat Expert 07/08/2018 - 04:41
can you share me few rows from your google sheet
User 07/08/2018 - 04:41
well . . maybe . . . hang on . . .
User 07/08/2018 - 04:43
Hmm it isn't pasting . . .
Excelchat Expert 07/08/2018 - 04:43
sure but i think i got it
Excelchat Expert 07/08/2018 - 04:43
this is your formula
Excelchat Expert 07/08/2018 - 04:43
=IF(AND($A5 = "VendorName",$A$1 >= A$2),A4,)
User 07/08/2018 - 04:43
Yes . . .
Excelchat Expert 07/08/2018 - 04:43
in general i ment not exact
User 07/08/2018 - 04:44
For some reason, Excel interprets the result of this formula as a 0 and not a blank.
Excelchat Expert 07/08/2018 - 04:44
yes excel and google sheets are different
Excelchat Expert 07/08/2018 - 04:44
so you need to explicitly mention =IF(AND($A5="VendorName",$A$1>=A$2),A4,"")
Excelchat Expert 07/08/2018 - 04:44
that way both excel and google sheet will give same result
Excelchat Expert 07/08/2018 - 04:44
so in your case =IF(AND($H5 = "VendorName",$D$1 >= AN$1),AM5,"")
User 07/08/2018 - 04:45
I don't think that works . . .
Excelchat Expert 07/08/2018 - 04:45
Why not?
User 07/08/2018 - 04:45
There is a difference between a blank and a null.
User 07/08/2018 - 04:46
nulls can be interpreted as numbers or values, but blanks can't.
User 07/08/2018 - 04:46
from what I can tell
Excelchat Expert 07/08/2018 - 04:46
yes so your other formulas mess up?
Excelchat Expert 07/08/2018 - 04:46
no "" is a text only
User 07/08/2018 - 04:46
yes,
User 07/08/2018 - 04:46
exactly.
Excelchat Expert 07/08/2018 - 04:46
1 min
Excelchat Expert 07/08/2018 - 04:46
D1= =ISBLANK(C1)
Excelchat Expert 07/08/2018 - 04:46
with your formula
Excelchat Expert 07/08/2018 - 04:47
and false with ouhmm
Excelchat Expert 07/08/2018 - 04:47
ok..
Excelchat Expert 07/08/2018 - 04:47
give me few min
User 07/08/2018 - 04:47
sure!
Excelchat Expert 07/08/2018 - 04:51
are you using Isblank function?
User 07/08/2018 - 04:52
No
User 07/08/2018 - 04:52
wait . . .
User 07/08/2018 - 04:52
just a sec . . .
Excelchat Expert 07/08/2018 - 04:52
just want to see the associated formulas
User 07/08/2018 - 04:53
=IF(ISNUMBER(AN5),AN5,AM5)
Excelchat Expert 07/08/2018 - 04:54
in that case "" should not matter
Excelchat Expert 07/08/2018 - 04:54
since "" is not number but 0 is
User 07/08/2018 - 04:54
Well, it does apparently when it's exported.
User 07/08/2018 - 04:54
No, what happens is that the result '0' is interpreted as a number.
Excelchat Expert 07/08/2018 - 04:54
yes
Excelchat Expert 07/08/2018 - 04:54
so instead
Excelchat Expert 07/08/2018 - 04:55
use this =IF(AND($H5 = "VendorName",$D$1 >= AN$1),AM5,"")
User 07/08/2018 - 04:55
if AM is 132.32 and AN is 0, the result becomes 0, not 132.32.
User 07/08/2018 - 04:56
That is, when it's exported.
Excelchat Expert 07/08/2018 - 04:56
It really would help if you could share the google sheet
Excelchat Expert 07/08/2018 - 04:57
so that i can change the formula and export and see
User 07/08/2018 - 04:57
I'm trying to copy a few rows for you . . .
Excelchat Expert 07/08/2018 - 04:57
Thank you
User 07/08/2018 - 04:57
there are 32 tabs, and each tabe has about 100 or more rows . .
Excelchat Expert 07/08/2018 - 04:58
just few rows with common formula
Excelchat Expert 07/08/2018 - 04:58
like =IF(AND($H5 = "VendorName",$D$1 >= AN$1),AM5,)
User 07/08/2018 - 04:59
Well, the references aren't working . . .
Excelchat Expert 07/08/2018 - 05:00
okk
Excelchat Expert 07/08/2018 - 05:02
ok so row 1 right
User 07/08/2018 - 05:02
Actually, that looks as though it's working . . .
Excelchat Expert 07/08/2018 - 05:03
so E is 253 the right answer
Excelchat Expert 07/08/2018 - 05:03
?
User 07/08/2018 - 05:03
But the export isn't working . . .
Excelchat Expert 07/08/2018 - 05:03
1 min
Excelchat Expert 07/08/2018 - 05:03
i changed =IF($H$1 <= G$1,B1,"")
Excelchat Expert 07/08/2018 - 05:03
let us save in excel this file ..
User 07/08/2018 - 05:04
just a sec . . .
Excelchat Expert 07/08/2018 - 05:04
hmm the D gets messed
Excelchat Expert 07/08/2018 - 05:05
right?
User 07/08/2018 - 05:05
You're not seeing the formulas in row 3 . ..
User 07/08/2018 - 05:05
hang on . . .
Excelchat Expert 07/08/2018 - 05:05
no i'm not
User 07/08/2018 - 05:05
But yes . .
Excelchat Expert 07/08/2018 - 05:07
ok
Excelchat Expert 07/08/2018 - 05:07
what is in row 3
Excelchat Expert 07/08/2018 - 05:08
if you had to change Column E formula how many would have have to change?
User 07/08/2018 - 05:08
tons of them . . . hundreds
Excelchat Expert 07/08/2018 - 05:09
ohh row 3 will work with my solution of ""
Excelchat Expert 07/08/2018 - 05:09
not row 1
User 07/08/2018 - 05:09
We are looking at google, not excel, right?
User 07/08/2018 - 05:10
It works in google.
User 07/08/2018 - 05:10
not excel.
User 07/08/2018 - 05:10
if you export this to Excel, you'll see.
Excelchat Expert 07/08/2018 - 05:11
yes row 3 is good row 1 D is the problem
[Uploaded an Excel file]
User 07/08/2018 - 05:11
The highlighted formula results in a 0 not a blank.
Excelchat Expert 07/08/2018 - 05:11
=IF($H$1 <= G$1,B1,"") will result in "" not 0
Excelchat Expert 07/08/2018 - 05:11
=IF($H$1 <= G$1,B1,) will result in 0
Excelchat Expert 07/08/2018 - 05:11
in excel
User 07/08/2018 - 05:12
Yes, but then the other formula =IF(ISNUMBER(C3),C3,B3) won't work.
Excelchat Expert 07/08/2018 - 05:12
can you see the screen shot it sent
User 07/08/2018 - 05:12
it will take the 0 and not the other value.
User 07/08/2018 - 05:13
sure . . . hang on . . .
Excelchat Expert 07/08/2018 - 05:13
=IF(ISNUMBER(C3),C3,B3) will work always if you change c1 to =IF($H$1 <= G$1,B1,"") even in excel
Excelchat Expert 07/08/2018 - 05:14
what will not work is d1 formula =B1-C1 as you are trying to subtract 1number and 1 text
User 07/08/2018 - 05:14
There you go.
[Uploaded an Excel file]
Excelchat Expert 07/08/2018 - 05:14
yes it is the old formula
Excelchat Expert 07/08/2018 - 05:14
=IF($H$1 <= G$1,B1,)
User 07/08/2018 - 05:15
The point is that it work work if the result is "".
[Uploaded an Excel file]
Excelchat Expert 07/08/2018 - 05:15
yes i see it
Excelchat Expert 07/08/2018 - 05:15
did you change the formula i sent?
Excelchat Expert 07/08/2018 - 05:15
did you add a ""?
User 07/08/2018 - 05:16
No, it won't work . . .
Excelchat Expert 07/08/2018 - 05:16
ok finally .. you would have to change formulas as google sheet and excel behave differently
User 07/08/2018 - 05:16
I get a value error.
Excelchat Expert 07/08/2018 - 05:17
for column D where you subtract C1 right?
User 07/08/2018 - 05:17
yes.
Excelchat Expert 07/08/2018 - 05:17
ok
User 07/08/2018 - 05:18
So, it looks as though there's no real solution.
Excelchat Expert 07/08/2018 - 05:19
just a min
Excelchat Expert 07/08/2018 - 05:19
not with 0 impact..
User 07/08/2018 - 05:19
Maybe there's a way to tell excel to interpret "" as a null.
Excelchat Expert 07/08/2018 - 05:19
we have to decide the min impact
Excelchat Expert 07/08/2018 - 05:20
I suggest either you choose to keep 0 and check for if(c1<>0....
User 07/08/2018 - 05:20
No, they rejected that solution.
User 07/08/2018 - 05:20
I did ask.
Excelchat Expert 07/08/2018 - 05:21
or keep =IF($H$1 <= G$1,B1,"") then check in D1 if c1 is "" then substitue that wih 0
User 07/08/2018 - 05:21
Now that's an interesting idea.
Excelchat Expert 07/08/2018 - 05:21
=B1-if(c1="",0,c1)
User 07/08/2018 - 05:22
I can play with that.
Excelchat Expert 07/08/2018 - 05:22
or 1 more option but i feel it has lot of impact
Excelchat Expert 07/08/2018 - 05:22
add a new column before C
User 07/08/2018 - 05:22
no, that isn't possible.
Excelchat Expert 07/08/2018 - 05:23
the best option is the null in the formula
User 07/08/2018 - 05:23
It would mean adding 12 columns.
Excelchat Expert 07/08/2018 - 05:23
only impact would be where you actually perform subtraction..
Excelchat Expert 07/08/2018 - 05:23
ohh
User 07/08/2018 - 05:23
Not really l. . it also impacts the other forumula . . .
User 07/08/2018 - 05:23
=IF(ISNUMBER(C1),C1,B1)
User 07/08/2018 - 05:24
But I might be able to deal with that.
Excelchat Expert 07/08/2018 - 05:24
ya thats the only way out..
Excelchat Expert 07/08/2018 - 05:25
next time try to build in excel 1st then upload .. usually that has better compatability
User 07/08/2018 - 05:25
Nah, they did this in GS . . .
Excelchat Expert 07/08/2018 - 05:25
hmm
User 07/08/2018 - 05:25
And that's what they want to work with.
Excelchat Expert 07/08/2018 - 05:26
ok. then why do they need it on excel?
User 07/08/2018 - 05:26
they need to export it for other reasons.
User 07/08/2018 - 05:26
I don't quite get it either.
Excelchat Expert 07/08/2018 - 05:27
:) ok
User 07/08/2018 - 05:27
I'll try to work with changing the formula.
Excelchat Expert 07/08/2018 - 05:27
you will have to change few formulas though
Excelchat Expert 07/08/2018 - 05:27
:)
User 07/08/2018 - 05:27
Thank you for your time.
Excelchat Expert 07/08/2018 - 05:27
please do click on end session and leave a feedback
User 07/08/2018 - 05:27
Well, I may be able to make a global change. We'll see.
User 07/08/2018 - 05:27
Thanks again!
Excelchat Expert 07/08/2018 - 05:27
have a nice day

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