Formula Auditing

**shakra** · 07-24-2012, 01:03 AM

Hi,

I did my Excel Expert Exam last week but unfortunately, I failed.
Sooo... I need your help for the following points:

1) How can we trace precedents for inconsistent formula and the table opened was full of formulas (E.G: SUM(A1:D1)...in same way for other cells)

2) How can we Evaluate long formula and correct the value of a cell (E.G: incorrect value for a cell using multiple IF function).

**dilipandey** · 07-24-2012, 01:11 AM

HI Shakra,

For formula auditing, you can go to formula ribbon tab and there you will find Trace precedents, Trace Dependents, Show formulas, Error checking and Evaluate formula kind of buttons

To evaluate long formulas, you can use evaluate formula button. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 01:22 AM

Hey Dilip,

Thanks for your kind reply. Ya all these buttons are in Formula Auditing group for Excel 2010.. I need to know that if I have a sheet with full of furmula like Sum(M23:Q23) so on, or like SUM(A11:N11) what will be the cell for inconsistent formula???

**dilipandey** · 07-24-2012, 01:26 AM

Hi Shakra,

Inconsistent formulas can be easily traced... they'll have small green icon on top left side

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 01:33 AM

I got red Icon on top left corner for one cell and there's no green Icon, wondering if it's the same!!!

**dilipandey** · 07-24-2012, 01:38 AM

Hi Shakra,

yes.. that should be same.
I believe you have Red chosen under field "Indicate errors using this color" in Options.

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 01:44 AM

This was in practis test

.

Thanks a ton for your help

One thing I couldn answer which was on evaluate Formula

as the Formula was too long using IF Function

**dilipandey** · 07-24-2012, 01:49 AM

Cheers

For evaluating long formula, you can always use Evaluate formula button (and how to use this button, you can google / or watch uTube videos

)

Also, if you are know the function syntax-es, you can select complete function syntax portion in a long formula and press F9 and that portion would be converted into output result and using this approach, you can easily break down long formula in small results for quick evaluation purpose. - sounds tricky

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 01:58 AM

oh wow greart!!! i will try F9

Many thanks Dilip you solved my problem. Do you have a sample excel sheet file to practice evaluate formula and repair the value??

**dilipandey** · 07-24-2012, 02:24 AM

You are welcome Shakra...

For sample file with long formulas, keep a watch on this forum OR you can create yours as well ... happy learning and Best of luck

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 02:28 AM

Thanks Dilip will do that

**dilipandey** · 07-24-2012, 02:30 AM

Sure.. and feel free to get back in case of any issues

Regards,
DILIPandey

<click on below 'star' if this helps>

**shakra** · 07-24-2012, 02:31 AM

Great

appreciate your help and support!!

Formula Auditing

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Re: Formula Auditing

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions