Formula editor for Excel 6

[philrock]

Is there an Excel add-in to make it easier to enter lengthy formulas? ... so while you're entering the formula, it looks like a textbook math formula, with parentheses, braces, brackets, horizontal lines to separate numerator and denominator of fractions with lots of characters? Sort of like MS Word Equation Editor, but more user friendly.

 

[GlennUK]

No, not that I know of, but I enter hard returns within formulas to break them up and make them easier to read, like:

=IF(ISNUMBER(K872),
IF(INDEX('Acq - Series'!$AP:$AP,P872)="ACTUAL",
INDEX('Acq - Series'!$AQ:$AQ,P872),
IF(INDEX('Acq - Series'!$AP:$AP,P872)="RELATIVE",
O872+INDEX('Acq - Series'!$AR:$AR,P872),
IF(INDEX('Acq - Series'!$AP:$AP,P872)="BOTH",
MIN(INDEX('Acq - Series'!$AQ:$AQ,P872),O872+INDEX('Acq - Series'!$AR:$AR,P872)),IF(INDEX('Acq - Series'!$AP:$AP,P872)="FIXED LENGTH",K872+INDEX('Acq - Series'!$AQ:$AQ,P872),"")))),"")

You do that by doing Alt-Enter at the appropriate place within the formula.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

good tip Glenn!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[lionelhill]

what a clever idea! Thanks Glen.

I tend to split complicated formulae over several columns of intermediate results. These columns can be hidden later if desired.

 

[GSCaupling]

I like that Glen.

Folks should be aware that they need to press F2 to see the whole formula in the worksheet; In Excel 2007, at least, only the formula segment before the first Alt-Enter shows in the formula bar.

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]

 

[SkipVought]

You can also TEST parts of your formula in the EDIT mode, by selecting an expression and hitting the F9 key.

CAVEAT: Remember to follow that by hitting the ESC key to return the expression, before hiting ENTER to exit the EDIT mode.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Let me clarify by explaining that you can select a reference, function or expression within your formula. It myst be complete, however.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Hi GS,

I like that Glen.

Folks should be aware that they need to press F2 to see the whole formula in the worksheet; In Excel 2007, at least, only the formula segment before the first Alt-Enter shows in the formula bar.

GS

You can alter the height of the formula bar in Excel 2007 to whatever you want, and toggle between that height and single line by clicking the double arrow button in the formula bar.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[philrock]

Great ideas... Thanks for your help.

 

[GSCaupling]

I don't where you're gonna keep all these stars, Glenn.

I had no idea about changing the height of formula bar. Thanks!

GS

[small][navy]**********************^*********************
I always know where people are going to sit. I'm chairvoyant.[/navy][/small]