# Evaluating a formula



## schielrn (Apr 23, 2009)

I just thought I'd put it out there to see what people use, since I have seen it debated for evaluating formula results.


----------



## Joe4 (Apr 23, 2009)

Other: Microsoft Word!

I copy the formula over to Word, and break it down it outline form, almost as if I was writing idented VBA code (its about the only thing Word is good for!). Makes it easy to see if I am missing parentheses and such (like for those nested IF statements).

That's just the way I think! It works for me.


----------



## SydneyGeek (Apr 23, 2009)

I tend to use Formula Auditing -- pure habit, but will check out the F9 trick too. 
And I agree with Joe; with a megaformula, sometimes the only way to see what you did is break out the text into Word or Notepad, and lay out each step. 

Denis


----------



## PaddyD (Apr 23, 2009)

Just FYI you can somewhat break down the formula directly in the formula bar using alt + enter to separate the components.


----------



## Greg Truby (Apr 23, 2009)

I like the step in functionality of the Evaluate Formula tool, though if it's hard enough that I cannot just write it and would need to break it down, then I probably split it into several cells at the beginning and got each component to work and then assembled it into an überformula. For array formulae, definitely the Evaluate Formula tool.


----------



## RichardS (Apr 24, 2009)

I'm like Mr Truby. For a complex formula with nested functions, I'll break it into it's various components to ensure the sum of all the components gives me the reult I'm looking for, then bundle it all together into one formula.


----------



## Stormseed (Apr 24, 2009)

Same here. Breaking up the whole formula into smaller chunks and executing each chunk seperately and I use one of the software called "Editplus" for Arrays and large complex functions.


----------



## Jon von der Heyden (Apr 24, 2009)

I like the auditing tool and the step in functionaility, but it doesn't always break down every component.

For me I also occassionally use the function wizard, particularly where the formula involves many worksheet functions.  The formula wizard will show the result of each worksheet function and the arguments of each.  I find that very useful...


----------



## schielrn (Apr 24, 2009)

Jon von der Heyden said:


> For me I also occassionally use the function wizard, particularly where the formula involves many worksheet functions. The formula wizard will show the result of each worksheet function and the arguments of each. I find that very useful...


I didn't think of this either, as I have done this before as well.


----------



## erik.van.geit (Apr 24, 2009)

Using same methods as already explained:
Breaking down into cells
Putting it on several lines with alt+enter
Sometimes, when tired or in a playful mood: trial and error and a lot of chance 
Often just looking over Aladins shoulder 

kind regards,
Erik


----------



## texasalynn (Apr 24, 2009)

I use the formula dialog boxes and click on each function to see what shows as the results for that section.


----------



## DreamAlchemist (Apr 24, 2009)

I tend to use the auditor, though if the formula is too long for it I will break the formula down on another worksheet and use substitutions as I recreate the formula across multiple cells.


----------



## ExcelChampion (Apr 27, 2009)

I'd like to know how some of the heavy hitters around here do it...Aladin, Dominic, etc...


----------



## Cbrine (Apr 28, 2009)

I believe that Aladin uses magic


----------



## Stormseed (Apr 28, 2009)

Cbrine said:


> I believe that Aladin uses magic



Exactly! Even his name reflects the *Jinn* caliber


----------



## erik.van.geit (Apr 28, 2009)

ExcelChampion said:


> I'd like to know how some of the heavy hitters around here do it...Aladin, Dominic, etc...


Perhaps send them a little PM with a link to this thread?


----------



## xenou (May 13, 2009)

> Putting it on several lines with alt+enter



Neat idea, Erik.  

With a tough formula I usually use F9, and sometimes work out the pieces in multiple cells.  Almost no experience with the evaluation tool - but sometimes I'll enlist it when asked by others for help with a formula that's not working (seems to be a nice way to jump into a formula you've never seen before).


----------



## T. Valko (May 14, 2009)

I use the evaluate formula command.

However, on certain types of complex array formulas (and some not so complex!) using evaluate formula will crash Excel 2002. I've ranted about this in the MS Excel ngs.

I've learned to recognize formulas that will do this and avoid evaluating them unless I'm really stuck on troubleshooting the formula (doesn't happen that often! ). In that case I reduce the referenced ranges to about 5 or 10 cells.


----------



## Stormseed (May 14, 2009)

> on certain types of complex array formulas (and some not so complex!) using evaluate formula will crash Excel 2002


 
Even I have used Excel 2002 for quite a while and I have never experienced this issue anyday. Perhaps, the Processor and the RAM too play a major role while computing these array formulae over large ranges ?


----------



## Colin Legg (May 14, 2009)

Something which has been mentioned yet... for array formulas I often use array ranges to break down each array component. Particularly handy to get to grips with the dimensions of an array or to use with large arrays - sometimes I find a long list of commas and semicolons hard to picture in my head.


----------



## RoryA (May 14, 2009)

Stormseed said:


> Even I have used Excel 2002 for quite a while and I have never experienced this issue anyday.


 
I guess you've never tried to evaluate the right (or wrong!) sort of formula then...


----------



## Stormseed (May 14, 2009)

rorya said:


> I guess you've never tried to evaluate the right (or wrong!) sort of formula then...



Yeah, that could be a possibility because I use a different software to evaluate complex arrays over large ranges. But this sounds strange overall and I have never heard of Excel getting crashed just by evaluating an array formula yet from any of my friends using Excel over these years.


----------



## RoryA (May 14, 2009)

Well, if #NAME? is who I think he is, it's safe to say he knows what he's talking about!


----------



## T. Valko (May 14, 2009)

Here are some results of a Google search in which I talk about Excel crashing when using evalaute formula:

http://groups.google.com/groups/search?q=excel+crash+biff

I couldn't filter the results any better but on the first page there are a few links on the subject.

So far, Excel 2007 doesn't exhibit this behavior, no crashes!


----------



## NateO (May 14, 2009)

rorya said:


> Well, if #NAME? is who I think he is, it's safe to say he knows what he's talking about!


 
If you think he is, who I know he is, he is. Caught my attention last weekend. Welcome, Biff! 

I usually use F9 or the Function Wizard. These both have unique issues, F9 often requires you to drastically reduce the Range in question. And on occasion, I've seen the Function Wizard return the correct result, while Excel will not...


----------



## T. Valko (May 14, 2009)

NateO said:


> If you think he is, who I know he is, he is.


Well, I hope I'm who rorya thought I was!


----------



## NateO (May 14, 2009)

#NAME? said:


> Well, I hope I'm who rorya thought I was!


 The one - the only - the inventor of the internet! Welcome, Al!


----------



## RoryA (May 14, 2009)

#NAME? said:


> Well, I hope I'm who rorya thought I was!



Yep! 
And Nate's "outed" you now, which saves me making cryptic comments about humming 'Ride of The Valk*o*ries' to myself...


----------



## cornflakegirl (May 15, 2009)

Couldn't you lot just get a secret handshake or something?

(Is there a Kipper amongst the MVPs too?)


----------



## RoryA (May 15, 2009)

cornflakegirl said:


> Couldn't you lot just get a secret handshake or something?



There is one, we just can't use it online!


----------



## Jon von der Heyden (May 15, 2009)

rorya said:


> There is one, we just can't use it online!


 
OH... MY... GOD!!!!


----------



## texasalynn (May 15, 2009)

rorya said:


> Well, if #NAME? is who I think he is, it's safe to say he knows what he's talking about!



Well I want to know how rorya knows someone by their cryptic name?  Must be a secret code there. LOL :wink:


----------



## cornflakegirl (May 15, 2009)

Well, Mr Name put in his profile that he's been an MS Excel MVP since 2007, and gave his location. So I'm guessing Rory/Nate didn't have to tax ze leetle grey cells too much. (Not that I could work it out from that information. But I am not as clever as them.)


----------



## IvenBach (May 15, 2009)

I have learned a few new techniques from reading this thread. Thank you. I have most always used the formula evaluator and alt+enter to better understand hard folmulas. If that fails then I come here for answers.


----------



## NateO (May 16, 2009)

cornflakegirl said:


> Well, Mr Name put in his profile that he's been an MS Excel MVP since 2007, and gave his location. So I'm guessing Rory/Nate didn't have to tax ze leetle grey cells too much. (Not that I could work it out from that information. But I am not as clever as them.)


 
I can't claim to be clever on this one - I cheated. And I outed him before he added that to his profile, in a certain thread. 

With his permission. Not sure if Rory saw that or not...


----------



## RoryA (May 17, 2009)

I saw your post in the NG so it wasn't that big a stretch!


----------

