• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Expanding Formula evaluation window

lohithsriram

lohithsriram

Active Member
Hello All,

Is there a way we can expand the Formula Evaluation window in Excel? This is for sure a great addition in Excel but has a drawback with a smaller display, if you want to evaluate the results on a bigger formula.

Any alternates/customized addins available over web to show this a better way? Thanks.
 
Hi Lohit...
Why do you need to resize a control less sub-window, which only has a scrollbar to control, and in few case tree view of inner formula..

we have Narayan to expand and enlarge formula.. one of my best cool formula evaluator.. :cool:
 
Thanks DEB and Faseeh,

Its our Formula challenge crackers I was trying to analyse on the Formula evaluation window and was not able to see the results of the formula expansions in one view. Hence the request came up.

It is one feature in Excel widely used but Microsoft has never shown interest in making changes to it. Ideally, a bigger interface with color differentiation of the parts of formula (like paranthesis comes on different color while we edit the formula cell) will definitely makes life easier for all the Excel formula forensicists.
 
1. I normally use J-Walk's mega formula trick to understand a formula. Split out basic units which work to generate the final mega formula. However, it is not always easy and possible to do especially with most array formulas.

2. Reduce the size range to see its way of working in Evaluate form. Useful if you want to understand working of a formula which has been applied to a larger range which makes it unwieldy.

3. You can use F9 key in formula bar which I think you already might be using. If you don't then go through this:
http://www.excelexperts.com/Excel-Tips-Function-debugging-using-F9

In principle, it is time you spend on formulas and understanding that gets developed by usage helps more. This makes your formula use more organic and intuitive.

Hth,
 
@SirJB7 : Thank you. I will go through the reference links and come back to you for any clarifications.

@Shrivallabha : J-Walk's mega formula is a great tip to construct the big formulas. I will try it out too. Thanks for sharing it.

Otherwise, Can API functions/.Net features do anything here to get us the control of the textbox in the Formula evaluation window, to reuse it on any custom forms or copying the text that displays in the evaluate window?
 
@SirJB7,
Thanks again for that link. I had gone through the link, used the addin from the site (though there is an issue with the link to the updated version). I will tell you, code in there is out of my reach. Fantastic coding by Rob. Thank you Rob.

The addin does a good job of split & nesting the formula to easily readable, but cannot calculate the values as we do on the formula bar using F9. But, as the site says that the author is ok with the re-use of code and licencing, I am aiming at creating an option which nests my formula in the formula bar and makes easy to read. This is how I can see my formula after customizing the code.

This is my formula (=MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1,MIN(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},A3),""))+1),"")),250)) [copied from one of the formula challenge series. :) ]

And after using the downloaded code + customization . I can see it in this way(actually with color codes for paranthesis), for a first shot, I liked it.

Code: 
=
MID(
    A1,
    MIN(
        IFERROR(
            SEARCH(
                CHAR(
                    ROW($65:$90
 
                    )
                ),
                A1,
                MIN(
                    IFERROR(
                        FIND(
                            {1,2,3,4,5,6,7,8,9,0},
                            A1
                        ),
                        ""
                    )
                )+1
            ),
            ""
        )
    ),
    250
)
 
Did you know you can place returns in your own formulas in the formula bar (alt + enter) ? It's not as nice, but helpful all the same :)
 
Xiq, Yes. (Alt + Enter) does the same. But we turn lazy enough to do it manually when the code does that job for you. :D
 
Also, best part of that code is it nests your formula like any smart code editor which makes easily readable. We need to make more exercise on Alt+Enter to get the same.
 
A search in object browser shows that only few properties / methods of this dialog are accessible. e.g.
Code: 
Public Sub Test()
Dim objDlg As Dialog
Set objDlg = Application.Dialogs(xlDialogEvaluateFormula)
[A1].Select
objDlg.Show
End Sub
 
Hi, lohithsriram!
I have an undelayable compromise with a couple of Carlsberg within... a few minutes (just have to check the freezer). So when I come back later maybe I find your analysis of the monster here. :cool:
Regards!
PS: Just in case, prior testing the add-in with that Nessie, save all current opened files. Claims won't be accepted.
 
SirJB7,
Ingoring your PS cost me (but not much though), managed to crash Excel with the downloaded addin. But this is awesome addin to nest the formulas. I will rely on this hereafter, if I want to analyse any formulas copied from others.

Also, the nested formula from this addin cannot go into my Excel formula bar because the formula length after nesting is 28173 (Yes.. its a MONSTER formula) characters and gives me a msg

Formula is too long. Formulas may not exceed 8192 characters. Thats something new I learnt that Formula too have its limit.
 
Hi, lohithsriram!

I swear that I was pretty sure that either ignoring or not my PS, trying the add-in with that weird and mad formula will crash Excel. And believe me if that's why I tried (sucessfully! :)) to tempt you to do the dirty job. :rolleyes:

But despite of this :p, the add-in is absolutely amazing not only for analyzing others' formulas but to correct one self's! How many times while building a little monster of your own, and begin closing parenthesis and doing the -apparently- easy job of adding ,"" or ,0 or any silly things, you get a syntax error? I'm an honorary member of that club. :(

About lengths and other stuff enter the help menu (F1 or "?" question mark symbol) and type "excel limits". You'll find an entry (usually the 1st) with all (all? who knows? I just discovered that there is an internal limit of the formula of 16K besides the yet known content formula length of 8K) the Excel Specs & Limits.

And please don't ask:
a) why the row height is limited to 409 points and not 512, 256, or 388 to approximate a bit
b) why the page breaks are 1026 and not 1024 (where does it holds the 2 excedents, one on each hand?)
c) why the max cell formats or styles is 64000 and not 65535/6 (should they have tried to write 64k?)
d) why the workbook max number formats is between 200 and 250 depending on the language version (256 was so weird? and what's the relation between the language and those formats? my car is red, just fyi)
e) why the max adjustable cells in Solver is 200? (256? no, 192, no? 197,3658 neither)
f) why the no. of shown elements in dropdown filter lists is 10000? (... any joke is suitable)
g) why the worksheet dependency is 64000? (same as c)...)
h) why the single cell dependency is 4000 millions? (have they truncated for easy reading? we're at specs window!)
i) wtf is Last Date Allowed in a Calculation? set to 4/3/07 (I have dd/mm/yyyy configuration but I don't know if that's March 4th or April 3rd... and of what year?)

Specs & Limits for 2010 version.

Have a nice reading.

Regards!
 
You must log in or register to reply here.
Back
Top