Excel IF ISBLANK substitute

Attached is the spreadsheet I am working on, in the Xero Template worksheet in column Q I have created the following formula: =IF(ISBLANK(A2),"","1")

I works ok but I would rather not see the 1 if A2 is blank, unfortunately i am using the same formula in Column A2 so column Q does thing there is something there. Is there a viable substitute that would accomplish the same thing and avoid having the 1 generated across all records?

Also, in column T I have the following formula: =IF(P2="Product charges",400,501) again it works ok but I am trying to avoid having any numbers show if the fields in column P2 are blank. Column P however has a ISBLANK formula as well.
Amazon-Export-to-Xero-Sample.xlsm
joeserroneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NinjaStyle82Systems AdministratorCommented:
what would you rather be there instead of 1?
0
joeserroneAuthor Commented:
Nothing, just blank
0
NinjaStyle82Systems AdministratorCommented:
Like this with the empty quotes?

=IF(ISBLANK(R4),"","")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ryan ChongCommented:
>=IF(ISBLANK(R4),"","")
This formula doesn't make sense to me, it's quite meaningless as both conditions set the value to blank.

For col Q ("Quantity"), the formula:

=IF(ISBLANK(A2),"","1")

is working fine. You can simply removed the values of Amazon from row A7 onwards, and the quantity "1" in col Q will be displayed as blank.

For col T, try:

=IF(P2="","", IF(P2="Product charges",400,501))

Open in new window


will these work for you?
Amazon-Export-to-Xero-Sample-b.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
The formula ISBLANK( ) is quite misleading by its name. Actually the correct name for this formula should be ISEMPTY( ) or ISNULL( ). lol

Why?
Because people try to use this formula in a logical statement and assume it to be evaluated as True if they see a blank cell which is being tested in the logical statement.
But that might not be always true as when you see a cell blank i.e. you don't see a value in a cell that doesn't always mean that the cell doesn't contain any content in it i.e. it might also contain a formula which is returning a blank.

For example =ISBLANK(A1) will return True only if A1 doesn't contain either a value or a formula in it i.e. it is completely an empty cell.

Personally I never use ISBLANK and would rather use in this case =IF(A1="","",1) i.e. this formula will return a blank if cell A1 is looking like a blank cell no matter it contains a formula which is returning a blank in it and will return 1 if cell A1 contains a value, whether entered manually in it or returned by a formula.

And once you understand this logic, you will be able to adjust all your formulas easily.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joeserroneAuthor Commented:
Thank you very much for your input on this.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Joeserrone!
Glad you found it helpful. :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.