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.