How To Hide Zero Values and Incomplete calculations
How To Hide Zero Values and Incomplete calculations
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?
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.
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?
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
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) )
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) )
Re: How To Hide Zero Values and Incomplete calculations
There is not IFERROR() function in the Apache OpenOffice Calc. Are you using LibreOffice Calc?...If I use the =IFERROR...
Open Office 4.1.3
Windows 10
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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?
-------------------------
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
Yes, that is AOO. The AOO Calc has not IFERROR() function.
To multiply with a zero value is not an error. The result will be 0.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?
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
=IF(ISBLANK(A1);"The A1 is empty";A1*30)
Code: Select all
=IF(ISBLANK(A1);"";A1*30)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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..
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
Upload a copy of your file with dummy data, but with same structure and formulas.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..
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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.
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
Not needed THAT specific file. But we need a simple example that can show us how you use the formulas.I dont see why you need my file? I cannot give it to you, ever.. I'm sorry.
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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?
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?
Re: How To Hide Zero Values and Incomplete calculations
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?
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
Please upload your example file here.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?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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.
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
What error message? We can not see ANY error message without examining YOUR sample file.I get that the computer cannot display anything. That's ok.
The question is more HOW TO GET RID OF THE ERROR MESSAGE?
Otherwise you have got tips for eliminate error messages related to an empty cell:
Code: Select all
= IF(ISBLANK(somecell);"";calculation)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
...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.
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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
How would you enter the code that you just entered. With my code, so that you think it would work?
Its making me
Open Office 4.1.3
Windows 10
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
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.How would you enter the code that you just entered. With my code, so that you think it would work?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: How To Hide Zero Values and Incomplete calculations
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
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
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
=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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How To Hide Zero Values and Incomplete calculations
Awesome Thanks You!
Open Office 4.1.3
Windows 10
Windows 10
Re: How To Hide Zero Values and Incomplete calculations
=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.
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 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice