How To Hide Zero Values and Incomplete calculations

Discuss the spreadsheet application

How To Hide Zero Values and Incomplete calculations

Postby Lame » Sat Oct 07, 2017 8:50 am

I have a simple question:

I have two columns.

Column one has a number, column two multiplies this number by 30.

If I use the =IFERROR then it Columns 2 shows 30 if columns 1 is empty. And If I dont dont do that it shows an error.

Because Column 2 is TIMES (*) 145, then if the column 1 number is zero or empty it should not show 145, since 0*145= 0 and not 145 right?

What can I do in order to show only the outcome or and empty cell? :ucrazy:

EDIT: With my code, ''=(AC2)*145'' Displays the the outcome in Cell 2 where AC2 is cell 1.But then all the blanc cells show /DIV=0 or some kind of error.
When I insert =IFERROR, then all cells show only 0 when there is a summ, or 145 when there is no summ.
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby keme » Sat Oct 07, 2017 9:19 am

Strange.

Your signature indicates that you are using Apache OpenOffice 4.1.3, which is also what I have (albeit the Mac version, which functionally should be identical). The IFERROR() function is not implemented in my Calc, so that should always return an error. Are you by any chance using LibreOffice Calc or MS Excel?

Could you attach your file here, or at least copy the exact formulas from your spreadsheet cells and paste them here? Explaining what you intended the formula to do does not help much in this case.

To show nothing when column 1 does not contain a valid entry, you can either use formatting (do not show zero values) or condition (e.g. =IF(ISBLANK(A2);"";A2*30) )
User avatar
keme
Volunteer
 
Posts: 2598
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Sat Oct 07, 2017 9:20 am

...If I use the =IFERROR...

Open Office 4.1.3
Windows 10


There is not IFERROR() function in the Apache OpenOffice Calc. Are you using LibreOffice Calc?
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Sat Oct 07, 2017 9:33 am

https://postimg.org/image/75vimwmqu3/

-------------------------

I have two Cells,

[Cell one is a number lets say 1.5] [Cell 2 is ''cell 1 times 30 =(AC2)*30].

I don't know how to combine this code..

What I want to achieve is:

That cell two shows the outcome of whatever cell 1 one = * 30.
But not showing any errors on empthy cellsin cell 2. Or not showing 30 when cell 1 is empthy. Do you understand?
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Sat Oct 07, 2017 10:25 am

https://postimg.org/image/75vimwmqu3/

Yes, that is AOO. The AOO Calc has not IFERROR() function.


What I want to achieve is:

That cell two shows the outcome of whatever cell 1 one = * 30.
But not showing any errors on empty cells in cell 2. Or not showing 30 when cell 1 is empty. Do you understand?

To multiply with a zero value is not an error. The result will be 0.
If you want hide the results calculated by the 0 value of an empty cell, then you need use the ISBLANK() function to separate the two cases.

Cell1 :A1 containing a number or it is empty.
B1 containing the formula:
Code: Select all   Expand viewCollapse view
=IF(ISBLANK(A1);"The A1 is empty";A1*30)
or
Code: Select all   Expand viewCollapse view
=IF(ISBLANK(A1);"";A1*30)


If we misunderstood your problem, then you must upload your ODF type sample file here.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Sat Oct 07, 2017 11:38 am

Well I'm sure that code will work.

The question is more how to I combine it with my code that I already have?

I cannot upload the file because its to much sensitive information..
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Sat Oct 07, 2017 11:53 am

The question is more how to I combine it with my code that I already have?

I cannot upload the file because its to much sensitive information..


Upload a copy of your file with dummy data, but with same structure and formulas.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Sat Oct 07, 2017 12:03 pm

I dont see why you need my file? I cannot give it to you, ever.. I'm sorry.

This is the formula that I have.

[A1] = 123
[A2] =(A1)*30

Outcome reads #DIV/0! if there is nothing in the cell, So the cell should be empty and not show that error.
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Sat Oct 07, 2017 12:12 pm

I dont see why you need my file? I cannot give it to you, ever.. I'm sorry.

Not needed THAT specific file. But we need a simple example that can show us how you use the formulas.

Multiply.png
Multiply.png (13.19 KiB) Viewed 397 times


The multiplication never will give you an error message when one of the parameters equals to zero.

The #DIV/0 message is for the divisions but not for the multiplications.

(Otherwise not needed the parentheses in your multiplication formula for the reference to cell A1.)
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby keme » Sun Oct 08, 2017 8:37 am

Note that the #DIV/0! error message also appears in other cases where there is an implied division, even when you do not use the divide operator.
The AVERAGE() function is perhaps the simplest example. It will return that error if the range given does not contain any numerical entries.

Does this apply to any of the spreadsheet content that you will not reveal to us, and is it in any way linked to the multiplication where you see the error?
User avatar
keme
Volunteer
 
Posts: 2598
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Mon Oct 09, 2017 8:55 am

I dont see the error everywhere.

Its just where there is no multiplication, then there is the error. (so there is a 1 number in a 2 part multiplication = error).

How do I hide the error message?
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Mon Oct 09, 2017 1:09 pm

Its just where there is no multiplication, then there is the error. (so there is a 1 number in a 2 part multiplication = error).

How do I hide the error message?


Please upload your example file here.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Tue Oct 10, 2017 11:30 am

Sorry I can't upload any files.

I get that the computer cannot display anything. That's ok.

The question is more HOW TO GET RID OF THE ERROR MESSAGE?

/?Div.. It just looks untidy... I'm sure there is a simple code to not display this message right?

What is it? And how do I use it with the other code?

I guess I'm repeating myself maybe you cannot help me?

Thanks anyway.
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Tue Oct 10, 2017 11:37 am

I get that the computer cannot display anything. That's ok.

The question is more HOW TO GET RID OF THE ERROR MESSAGE?


What error message? We can not see ANY error message without examining YOUR sample file.
Otherwise you have got tips for eliminate error messages related to an empty cell:
Code: Select all   Expand viewCollapse view
= IF(ISBLANK(somecell);"";calculation)
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Tue Oct 10, 2017 11:56 am

...or use the LibreOffice if you want use the IFERROR() function anyway.

I'm repeating myself, too:
The AOO Calc have not IFERROR() function. You must combine the IF() statement and the ISERROR() function in the AOO Calc.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Tue Oct 10, 2017 12:05 pm

Ok so because I'm a newbie.

How would you enter the code that you just entered. With my code, so that you think it would work?

Its making me :knock: ;)
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Zizi64 » Tue Oct 10, 2017 12:13 pm

How would you enter the code that you just entered. With my code, so that you think it would work?


I do not know if it will work for you while I not see YOUR SAMPLE FILE (not the original one) and your formula in the sample file.
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.6 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.2 and AOO4.1.3
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 5913
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Tue Oct 10, 2017 1:25 pm

Well then you cannot help me.

I was hoping you could put in the code (A1 * 30 =) + (Your code) = outcome or at least not /Div?

And how YOU would do it.

If you cannot do that then I dont know what will work.

Its ok ill ask someone else.. Good luck, thanks for your help. Bye
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Villeroy » Tue Oct 10, 2017 1:46 pm

=if(iserror(A1/B1);"error";A1/B1)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24405
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How To Hide Zero Values and Incomplete calculations

Postby Lame » Tue Oct 10, 2017 1:55 pm

Awesome Thanks You!
Open Office 4.1.3
Windows 10
Lame
 
Posts: 9
Joined: Sat Oct 07, 2017 8:44 am

Re: How To Hide Zero Values and Incomplete calculations

Postby Villeroy » Tue Oct 10, 2017 2:11 pm

=IF(COUNT(A1:B1)<2;"Missing Number";IF(B1=0;"Can't divide by 0";A1/B1))
if the count of numbers in A1:B1 is smaller than 2 then return text "Missing Number", else if B1=0 then return text "Can't divide by 0", else return the result of A1/B1.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24405
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: FDF, Zizi64 and 32 guests