How to hide formula results until conditions are met

Hi marshall,

I don't understand your use of the SUM() function in your example. It looks like you are summing (A6÷2×1) and 0. Which would be A6÷2. I don't think you should be getting the error if A6 is blank. There is probably somethng else going on.

Anyway, you can test a cell for an entry with LEN()

IF(LEN(A6)>0, do your calculation, "") this will show nothing when there is no entry in A6.

If you want to bring the contents of a cell into another one, type "=" in the destination cell, then click in the cell that has the contents you want. type return.

quinn

Hi Marshal,

I've usually recommended using IF and LEN to control this.

The general form is:

IF(LEN(keycell)<1, "",formula)

For your case:

IF(LEN(A6)<1,"",(SUM(A6÷2×1,0))×(Services Data Sheet-1'::Table 1::AH6))

Looking more closely at the formula, I'm wondering why you've used SUM(a,b) with b set to a fixed value of zero and a set to A6÷2x1. you could simplify the formula part SUM(A6÷2×1,0) by removing the two identity values, 0 for addition (which also removes the need for SUM() ) and 1 for multiplication leaving A6÷2.

IF(LEN(A6),<1,"",(A6÷2)×(Services Data Sheet-1'::Table 1::AH6))

Regards,

Barry

Regarding the second question, =cell reference

where cell reference is the address of the cell whose contents you want to copy will do the job.

PS:

The only thing I see as likely to cause an error message from your formula is a text entry in A6 or in Services Data Sheet-1'::Table 1::AH6.

All of the arithmetic operators ( + - ÷ x ) will choke on text and throw an error.

What is the error message presented (click on the red triangle to read the message)?

Regards,

Barry

"im not sure why the 0 is there i think it was just a typo."

It might be there as Numbers was expecting at least two items in the list of amounts to be SUMmed, and automatically inserted a zero when you left the space empty, but didn't remove the separator ( , ).

Regards,

Barry

Hello Marshall,

Have you considered or tried =IFERROR((SUM(A6÷2×1,0))×(Services Data Sheet-1'::Table 1::AH6),"") as a solution to your original issue? I may have misread, but I think this is what you were asking for. Put very simply, if your formula isn't errant it returns the value, otherwise it returns "" (or nothing). Hope this helps.

Hi TheTubbyOne,

You are right that IFERROR() can return a null value if the formula fails. I don't believe it is failing because A6 is empty. Using IFERROR() can mask problems with a formula and short-circuit the actual solving of the problem.

quinn

Howdy t quinn,

That may be the case. That's why I gave myself an out ("I may have misread"). The way I read it I thought he was filling the formula down the sheet in preparation for future values.

marshall.lee.feather wrote:

the problem is i will not fill in A6 until need so all the way down my sheet i have these red triangle.

You're correct that my use of IFERROR() is heavy handed, but I normally prefer the simplest solutions to a problem. We all have opinions and they all have their individual benefits. I suppose it would have been better of me to fully explain the implications of my solution. Probably would have been a good idea on my part to test to see if referencing a cell actually caused an error. Either way, have a great day.

Hi Marshall,

Earlier, I wrote: "What is the error message presented (click on the red triangle to read the message)?"

So far there's been no response from you to that question.

When I set up a document similar to yours (two sheets, one table on each, and containing enough rows and columns to make cell references to the two cells in your formula), the formula, copied from your post and pasted into B6 of the first table, returns a 'Syntax error message'

If that is the error message you are receiving, the error is in the formula, and has nothing to do with there being data in cell A6 or not.

Re-examining your formula: =(SUM(A6÷2×1,0))×(Services Data Sheet-1'::Table 1::AH6)

The only error I see here is the missing single quote before Services Data Sheet-1' I suspect that was missed in retyping the formula into your message, as Numbers would have automatically included it in the formula if it was constructed by clicking the cell to be referenced.

Much of the formula is redundant, as has been mentioned above. There's no need to use SUM—all of the calculations being done are either multiplication or division, not addition. Nor is there any need for the parentheses not part of SUM—doing only division and multiplication, the standard order of operations will proceed from left to right, first dividing A6 by 2, then multiplying the result by AH7.

The formula below does the same job. The upper notation shows how the formula appears in Numbers '09, the lower notation shows the same formula as it appears in Numbers 3.

=A6/2*'Services Data Sheet-1'::Table 1 :: AH7

=A6÷2x'Services Data Sheet-1'::Table 1 :: AH7

The formula will show a result of 0 if there's not a number in A6. You can make it show a 'blank' cell by wrapping it in either of the IF statements posted earlier:

t quinn's: IF(LEN(A6)>0, A6÷2x'Services Data Sheet-1'::Table 1 :: AH7, "")

Barry's: IF(LEN(A6)<1,"",A6÷2x'Services Data Sheet-1'::Table 1 :: AH7)

Regards,

Barry