Increase size of Evaluate Formula Dialog Box

Copper Contributor

The Dialog box should be resizeable - it a must for long formulas.

 

havish_madhvapaty_0-1640787660590.png

 

7 Replies

@havish_madhvapaty 

 

Might I offer a suggestion? From the looks of that formula, just the small part that appears in your screen capture, it could be a case study in the kind of formula that can be written, but should NOT be. The textbooks on Excel all warn against llllloooonnnnngggg formulas because they become unintelligible, even to their original creator, and therefore more error prone and difficult to maintain even if they work when first created.

 

You'd be far better served by breaking this into several steps, doing the internal evaluations (formulas) with clear results at each stage along the way, maybe hiding the columns (or rows) containing the intermediate "helper" formulas. Rare is the case where that's not possible, and it makes for much greater clarity.

@havish_madhvapaty 

Such feedbacks better to publish on feedback portal Excel · Community (microsoft.com) , Microsoft monitors it.

 

This particular request exists for years. Microsoft makes dialog boxes resizable one by one, but formula evaluate is still postponed. You may add your vote here

Make all windows (Dialog Boxes) containing variable-sized content resizable · Community (microsoft.c...

Make the Evaluate Formula dialog bigger · Community (microsoft.com)

 

More votes - more chances it'll be done faster.

Hi @mathetes, thanks for suggestion. I have tried breaking this into several steps using  the intermediate "helper" formulas, but the current small sized "Evaluate Formula" dialogue box still doesn't prove to be very easy to use.

image.png

@abdullahabid 

 

It still looks to me as if you're working with a single formula; maybe a single formula with intermediate steps (nesting)...my suggestion was that you break it apart into separate formulas.

 

Is it possible for you to post the entire formula, not just what you're seeing in the "Evaluate Formula" dialog  box?

@mathetes 

FG_Availibility Formula is defined as follows and it further calls the multiple helper and intermediate functions:

=IFNA( IFS( CC_IsPast,"--", AND(NOT(CC_HaveTasks),CC_IsToday),"Target Achieved", AND(FI_MaxSlotsToday>=7,OR(CC_IsFuture,CC_IsToday)), "Available", AND(AND(FI_MaxSlotsToday>=5, FI_MaxSlotsToday<=6),OR(CC_IsToday,CC_IsFuture)), "Partially Available", AND(AND(FI_MaxSlotsToday>=2,FI_MaxSlotsToday<=4),OR(CC_IsToday,CC_IsFuture)), "Difficult", AND(FI_MaxSlotsToday=1,OR(CC_IsFuture,CC_IsToday)), "Very Difficult", AND(FI_MaxSlotsToday=0,OR(CC_IsFuture,CC_IsToday)), "NOT Available", AND(FI_MaxSlotsToday<1,OR(CC_IsToday,CC_IsFuture)), "Invalid Targets" ),"Add new Week in summary")

 

@abdullahabid 

I just realized you never actually say you're looking for help with this formula. The whole point up till now has been that "the dialog box for evaluating formulas needs to be larger."

 

So are you asking for help with the formula as well? 

 

I find it easier to analyze, though still not easy for a formula this long, to display it thus:

=IFNA(

IFS(
CC_IsPast,"--",
AND(NOT(CC_HaveTasks),CC_IsToday),"Target Achieved",
AND(FI_MaxSlotsToday>=7,OR(CC_IsFuture,CC_IsToday)), "Available",
AND(AND(FI_MaxSlotsToday>=5, FI_MaxSlotsToday<=6),OR(CC_IsToday,CC_IsFuture)), "Partially Available",
AND(AND(FI_MaxSlotsToday>=2,FI_MaxSlotsToday<=4),OR(CC_IsToday,CC_IsFuture)), "Difficult",
AND(FI_MaxSlotsToday=1,OR(CC_IsFuture,CC_IsToday)), "Very Difficult",
AND(FI_MaxSlotsToday=0,OR(CC_IsFuture,CC_IsToday)), "NOT Available",
AND(FI_MaxSlotsToday<1,OR(CC_IsToday,CC_IsFuture)), "Invalid Targets" ),
"Add new Week in summary")

 

And quickly scanning that I don't see any syntax errors.....so what (if you are seeking help with the formula per se) happens when it's in context?

@abdullahabid 

 

Aside, having nothing to do with the size of the Evaluate Formula dialog box....

 

Instead of the form

 

IFNA(IFS(condition1, result1, ..., conditionN, resultN), defaultResult)

 

it would be better to write

 

IFS(condition1, result1, ..., conditionN, resultN, TRUE, defaultResult)

or

IFS(condition1, result1, ..., TRUE, resultN)

 

Use the latter form only if resultN is the intended defaultResult.

 

The point is:  it is "good practice" to have a TRUE condition/result pair at the end of IFS expressions to avoid an unintended #N/A error, just as it is "good practice" to have a value-if-false expression at the end of IF expressions to avoid an unintended FALSE result.