Is it possible to comment out sections of formula in order totroubleshoot?

[Salmon Egg]

I often have problems getting formulas using Excel functions correct. In
part, the problem arises in trying to get a long formula in a single cell.
In typical programming languages, I would just break up the code into
several short lines that would be evaluated sequentially. In Excel, that
would screw up the appearance of the spreadsheet.

In Pascal It was possible to embed comments between (* and *) or { and }.
The interpreter or compiler would just ignore everything between such pairs
of symbols. I believe that various forms of FORTRAN and C are less
forgiving. Usually, a comment uses up a whole line or once started continues
to the end of the line.

My question is: Is there a simple way to "comment out" pieces of formulas in
Excel in order to check out the individual pieces before assembling them
into a complete formula?

Bill
-- Fermez le Bush--about two years to go.

 

[JE McGimpsey]

My question is: Is there a simple way to "comment out" pieces of formulas in
Excel in order to check out the individual pieces before assembling them
into a complete formula?

You can't comment out portions of formulae.

The best workaround, I've found, is to build your formulae in steps. For
instance, if you're working toward (and this is probably too simple, but
it illustrates the idea):

A1: =IF(J1+K1>3,IF((B1^2+2)<=(D1+3),SQRT(D1),
F1+G1),VLOOKUP(J1,M1:N15,2,FALSE))

you could use the following:

A1: =IF(A3,A2,A7)
A2: =IF(A4,A5,A6)
A3: =J1+K1>3
A4: =(B1^2+2)<=(D1+3)
A5: =SQRT(D1)
A6: =F1+G1
A7: =VLOOKUP(J1,M1:N15,2,FALSE)

Then, after making sure that A3:A7 gave the expected values for various
values in B1,D1,F1,G1,J1,and K1, substitute the parts of the formulae
after the = sign in A4:A6 back into A2, then substitute the formula in
A2,A3 and A7 back into A1..

 

[Bob Greenblatt]

I often have problems getting formulas using Excel functions correct. In
part, the problem arises in trying to get a long formula in a single cell.
In typical programming languages, I would just break up the code into
several short lines that would be evaluated sequentially. In Excel, that
would screw up the appearance of the spreadsheet.

In Pascal It was possible to embed comments between (* and *) or { and }.
The interpreter or compiler would just ignore everything between such pairs
of symbols. I believe that various forms of FORTRAN and C are less
forgiving. Usually, a comment uses up a whole line or once started continues
to the end of the line.

My question is: Is there a simple way to "comment out" pieces of formulas in
Excel in order to check out the individual pieces before assembling them
into a complete formula?

Bill
-- Fermez le Bush--about two years to go.

Or, in addition to J.E.s' suggestion, another technique that is pretty
helpful in debugging formulas is: with the formula being edited in the
formula bar, highlight a portion of the formula and then press F9. This
calculates only the highlighted string. You must remember to a) make sure
that what you have highlighted has valid syntax, i.e. the parentheses match,
etc., and b)remember to press the red X to cancel the edit when you are
done. Otherwise the evaluated stuff will become part of the formula in the
cell, replacing formula with fixed results.

 

[Salmon Egg]

Or, in addition to J.E.s' suggestion, another technique that is pretty
helpful in debugging formulas is: with the formula being edited in the
formula bar, highlight a portion of the formula and then press F9. This
calculates only the highlighted string. You must remember to a) make sure
that what you have highlighted has valid syntax, i.e. the parentheses match,
etc., and b)remember to press the red X to cancel the edit when you are
done. Otherwise the evaluated stuff will become part of the formula in the
cell, replacing formula with fixed results.

I have been doing something like what J.E. Suggests. I did not know about
the F9 feature that you suggest. I will give it a try.

What I am now doing is to use a text editor do generate intermediate steps.
I can use Word, but a simpler would do. I write down formulas in the formula
bar to check them out. If it works, I put it in a line in the editor. That
way I can cumulate snippets that work. Then I can write a more complicated
snippet such as an intricate comparison scheme and paste snippets into
place.

I am still investigating what appears to be a bug in which I get error
values returned using variable expressions even though they work with
explicit values. When I pin that down, I will post.

Bob, did your dad work at Hughes Aircraft?

-- Fermez le Bush--about two years to go.

 

[Salmon Egg]

I have been doing something like what J.E. Suggests. I did not know about
the F9 feature that you suggest. I will give it a try.

I tried using the F9 key as suggested but without results. I am using Excel
X for Mac Service release 1. It dates back to 2001. All that happens is that
all the windows shrink down to fit onto the screen. I see no indication that
part of a formula is being evaluated.

Bill
-- Fermez le Bush--about two years to go.

 

[JE McGimpsey]

I tried using the F9 key as suggested but without results. I am using Excel
X for Mac Service release 1. It dates back to 2001. All that happens is that
all the windows shrink down to fit onto the screen. I see no indication that
part of a formula is being evaluated.

That's because Expose (the MacOS function) is intercepting F9 before it
can get passed to XL. In the System Preferences/Expose, change or
eliminate that shortcut, and F9 will work in XL.

 

[CyberTaz]

OS X default assignment of the F0 key is for ExposE - go to AppleMenu>System
Preferences - Dashboard & Expose & change the F9 assignment

Regards |:>)
Bob Jones
[MVP] Office:Mac