Hi im looking for a tool that will assist me with writting large formula's. Ideally i would like to be able to view the different values that make up the overall formula whilst working on it. This would get rid of the need to break it down into many cells inorder to track the values being used.
I hope ive explained roughly what im looking for
Regards,
ceemo
Ive had a few views does anyone know of any such tool?
I've never heard of such a tool. Did you Google on this?
Good Luck
Bruce
The older I get, the better I used to be.
USA
no its just something i imagine would be good and if it does not exist i hope someone will create and offer me a free version for my idea.
It would be a handy tool do you not agree?
I don't know if this is exactly what you're looking for, but when I have a large and/or complex formula to write, I will write a user-defined function in VBA, especially if it's a function I'll use fairly frequently. While writing and debugging the code, I can monitor various values and operations through the watch window. Then, after I get the function all coded and debugged, I can enter that function into the cell(s) instead of having a large function enterred into a worksheet.
Just a thought, don't know if that would satisfy you or not.
thnx for the tip
ceemo,
Excel contains a couple of features (not sure about Excel97 been to long) in 2000 and XP which do what you are looking for. The first is to write your formulas straight into the function argument dialog box. As you complete each argument; the calculation is shown to the right. What more could you want?
For instance, If I'm writing a long formula that will begin with the IF function, instead of typing =IF(,,) and typing in the arguments, I will select my cell and type = (or in XP hit the fx on the formula bar) and then choose the IF function from the Insert Function dialog box. Now I have the Function argument dialog box for the IF function displayed. Now suppose my logical test argument needs to be another Function. I simply click in the Logical_test and then select my function from the drop down on left of the formula bar and two things happen, one your next function is inserted into your original formula and it's function argument dialog box is opened. Now when you get done filling out the arguments don't hit OK instead go up to the formula bar and click on the original IF, right on the word IF itself and the function argument for the IF function is now shown with the completed second function right in the Logical_test argument.
I have some bitmap images, showing this is use, but they are too large to attach. If you email me I would be happy to send them.
The second thing you should look into is under TOOLS>FORMULA AUDITING>EVALUATE FORMULAS. I use this alot to help figure out why I'm getting the results I am.
HTH
Casey
that great many thanx i will use this technique at home. unfortunatly at work i onbly have 97 which i find a pain.
Casey, EXCELLENT tip and very well presented. Funny, I, too, use that all the time and never thought to associate it with the OP's question!
Well Done!!
Bruce
This is what I use; the F9 button!
Go to the formula in Fx formula field (under all the Icons buttons).
Place the cursor over the part of the formula. Press F9 - this will display the values behind that part of the formula. (see encl. example)
Hope this helps
Ola Sandström
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks