Hello I have a simple if statement
IF($F$4=F6,(H6),)
What I would like is that if the statement is false, instead of putting $0.00, it just leaves it blank, but doesnt affect the calculations that link off that cell
ie if its zero the zero is not visible.
I can do it in Excel, but not sure how you do it in numbers.
thanks for any guidance on phrasing this statement.
For the 'simple if statement' presented in text:
IF($F$4=F6,H6,"")
This places a null string (a text value with no length) in the (unspecified in your description) cell containing the formula if F4's content does not match the content of F6, and gets the value from H6 if F4's content matches the content of F6.
The formula shown in the formula Editor is in G6, according to the selection rectangle in that cell.
IF($F$4=F6,H6/11,??)
Assuming that the tax rate in cell G3 is correct, the amount in H6 should be the tax included price of the item on this line. If that is true, then this formula calculates the amount of that price that is tax, and places that amount in cell G3.
If the value in F6 does NOT match the Y in F2, the formula will return whatever follows the comma visible at the right end of the displayed formula. To show this in your screen shot, you would need to either drag the bottom boundary of the formula editor box downward to expose the second line of the formula, OR Drag the editor to the left, then drag the right boundary of the editor right to make the box long enough to show the rest of the formula and it's closing parenthesis.
I would expect the contents of the out of sight part of the formula would be: "")
The parentheses enclosing H6 in the first formula and the ones enclosing H6÷11 in the second formula are not necessary
One problem with using the null string is that although it is invisible to us, the formula can still see it. For most function-based formulas, that's not a big issue, and the functions used will likely recognize text as a zero value.
But for formulas using the arithmetic operators (entered as + - * and /, and displayed as + - ÷ and × ) may choke on text values, including the null string) and return an error message rather than the expected result.
If that turns out to be the case with your document, rewrite your formulas to show a zero result where I've used a null string, then set a conditional highlighting rule for the cell that sets the text colour to white (or try 'none") for a zero result. The zero will still be there, and can be used in any math referencing that cell, but the zero won't be visible against the white background.
Regards,
Barry
For the 'simple if statement' presented in text:
IF($F$4=F6,H6,"")
This places a null string (a text value with no length) in the (unspecified in your description) cell containing the formula if F4's content does not match the content of F6, and gets the value from H6 if F4's content matches the content of F6.
The formula shown in the formula Editor is in G6, according to the selection rectangle in that cell.
IF($F$4=F6,H6/11,??)
Assuming that the tax rate in cell G3 is correct, the amount in H6 should be the tax included price of the item on this line. If that is true, then this formula calculates the amount of that price that is tax, and places that amount in cell G3.
If the value in F6 does NOT match the Y in F2, the formula will return whatever follows the comma visible at the right end of the displayed formula. To show this in your screen shot, you would need to either drag the bottom boundary of the formula editor box downward to expose the second line of the formula, OR Drag the editor to the left, then drag the right boundary of the editor right to make the box long enough to show the rest of the formula and it's closing parenthesis.
I would expect the contents of the out of sight part of the formula would be: "")
The parentheses enclosing H6 in the first formula and the ones enclosing H6÷11 in the second formula are not necessary
One problem with using the null string is that although it is invisible to us, the formula can still see it. For most function-based formulas, that's not a big issue, and the functions used will likely recognize text as a zero value.
But for formulas using the arithmetic operators (entered as + - * and /, and displayed as + - ÷ and × ) may choke on text values, including the null string) and return an error message rather than the expected result.
If that turns out to be the case with your document, rewrite your formulas to show a zero result where I've used a null string, then set a conditional highlighting rule for the cell that sets the text colour to white (or try 'none") for a zero result. The zero will still be there, and can be used in any math referencing that cell, but the zero won't be visible against the white background.
Regards,
Barry
If you don't want to change the formula, i.e. leave the zero but have it appear blank, then you should be able to apply a Custom Format, like this:
Select the cells in the column and go to the Data Format dropdown under the Cell tab:
Scroll down and choose Custom Format:
Add a rule that looks like this:
This displays 0s as blanks. But on my machine it drops the decimals for non-zero values even when specifying two decimal places.
That seems to be a bug. Maybe you will have better luck as you experiment with Custom Formats, which have similar effects to those in Excel.
SG
That worked a treat thank you so much.
I manage to solve the disappearing decimal point.
Select SHOW TRAILING ZERO in stead of NUMBER OF DIGITS.
Really appreciate you getting back to me.
Yes Barry that is right.
Conditional formatting thanks for that I can see that's another way of solving it.
Still getting use to number from Excel as I convert some of my worksheets over.
cheers
Numbers If statements when equal zero
