Excel formulas to show expanded nesting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

With my poor vision I have a hard time seeing the structure in a formula I am
writing or modifying. The built-in tools are useful, but I would like an
option to toggle the formula from this:

=IF($B26="","",ROUNDUP(INDEX(Refs!I:I,MATCH($M26,Refs!H:H,0))*K26,0))

to something like this:

=IF(.............................................................................................................................)

$B26="","",ROUNDUP(........................................................................*K26,0)

INDEX(...........................................................)

Refs!I:I,MATCH(.............................)

$M26,Refs!H:H,0

{difficult to format here without a monospace font}


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...f-4e5903a4a425&dg=microsoft.public.excel.misc
 
Excel will allow you to use alt-enters in your formulas--don't put them inside
strings (between ""'s).

And it forgives extra spaces between arguments.

I could use:
=IF($B26="",
"",
ROUNDUP(
INDEX(
Refs!I:I,MATCH(
$M26,Refs!H:H,0))*K26,0))


(Yep, I had difficulty seeing what you wanted.)

And remember that in xl97-xl2003, your formulas cannot exceed 1024 characters
(when measured in R1C1 reference style.)
 
Hi Dave,

Unfortunately, my formatting came out abysmally; all the leading spaces
disappeared and thus the sense was lost. I'll try again. What I was trying
to show is a way of displaying a formula so each level of nesting is set down
one line, thus making it easier to see the individual functions that make up
the whole formula.

Something like
=SUM(INT(A5:A12),INT(AVERAGE(SUM(B5:B12), C3)))

to be shown on multiple lines as:
.. =SUM( )
.. INT(A5:A12),INT( )
.. AVERAGE( , C3)
.. SUM(B5:B12)

Again it looks a mess until monospaced, but if you paste it into Notepad you
should see what I'm on about.
 
I read in plain text, so your post looks perfect. The bad news is that you
can't do this kind of thing.

But you could do:

=SUM(
INT(A5:A12),INT(
AVERAGE(
SUM(B5:B12),
C3)
)
)

But it eats up a lot(!) of characters.
 
Back
Top