Reporting Excel Formula Evaluation

Hello,

In Excel we have the option to evaluate a formula on the tab Formulas - Formula Auditing - Evaluate Formula.

This is a nice tool, but when it is a more complex formula, I would like to have the possibility to generate a report that shows the individual calculation steps and the outcome of each step in-between.

Like for example this formula:

=IF(OrgJobSelected="Operations",GETPIVOTDATA("[S] "&F$7,PVT_BW001_Actueel!$B$2,"Markt",$B8);
GETPIVOTDATA("[S] "&F$7,PVT_BW001_Actueel!$B$2,"Markt",$B8,OrgNiveau;OrgJobSelected))/1000000

I would like to analyze each step and produce a report like

=IF(
OrgJobSelected="Operations" = TRUE,
GETPIVOTDATA(
"[S] "&F$7 = "[S] Planomzet",
PVT_BW001_Actueel!$B$2,
"Markt",
$B8) = 437372299,04,
GETPIVOTDATA(
"[S] "&F$7 = "[S] Planomzet",
PVT_BW001_Actueel!$B$2,
"Markt",
$B8,
OrgNiveau,
OrgJobSelected
) = #REF!
) = 437372299.04 / 1000000
= 437.37229904

(In red are the added outcome values.)

Partly I am able to do that for simpler functions (I extract the parts between brackets and separated by "," and add the results per step),
but I don't succeed in distinguishing formulas like "FORMULANAME(" - extract code until next ")" -
without having to define an extensive list of possible formulas (and their number of possible arguments).

Does anyone see a solution for this?

Grtz, BartH

Hello Ali,
Regrettably, I am not able to include a sample of this workbook as it holds sensitive information about my client.
I was hoping the description was clear enough as it would apply to any formula.
Grtz, BartH

Hi Ali,
I do understand the essence of your message.
I'll see if I can make something up.
Grtz BartH