Page 1 of 1
How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 8:50 am
by Lame
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 9:19 am
by keme
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) )
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 9:20 am
by Zizi64
...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?
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 9:33 am
by Lame
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?
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 10:25 am
by Zizi64
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
=IF(ISBLANK(A1);"The A1 is empty";A1*30)
or
If we misunderstood your problem, then you must upload your ODF type sample file here.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 11:38 am
by Lame
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..
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 11:53 am
by Zizi64
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 12:03 pm
by Lame
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sat Oct 07, 2017 12:12 pm
by Zizi64
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 (13.19 KiB) Viewed 9090 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.)
Re: How To Hide Zero Values and Incomplete calculations
Posted: Sun Oct 08, 2017 8:37 am
by keme
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?
Re: How To Hide Zero Values and Incomplete calculations
Posted: Mon Oct 09, 2017 8:55 am
by Lame
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?
Re: How To Hide Zero Values and Incomplete calculations
Posted: Mon Oct 09, 2017 1:09 pm
by Zizi64
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 11:30 am
by Lame
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 11:37 am
by Zizi64
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
= IF(ISBLANK(somecell);"";calculation)
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 11:56 am
by Zizi64
...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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 12:05 pm
by Lame
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
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 12:13 pm
by Zizi64
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.
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 1:25 pm
by Lame
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
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 1:46 pm
by Villeroy
=if(iserror(A1/B1);"error";A1/B1)
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 1:55 pm
by Lame
Awesome Thanks You!
Re: How To Hide Zero Values and Incomplete calculations
Posted: Tue Oct 10, 2017 2:11 pm
by Villeroy
=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.