Resolution & Documenting

Ola · Mar 29th, 2002, 03:11 PM

Is there any easy way or existing tool that enables you to look at and resolve long complex formulas i.e.
=IF($I1273="Bmk",AW1273,IF(ISNUMBER(AJ1273),AJ1273,IF(AND($I1273="GenFollowerDerv",$B1273=1),BI1273,IF(LEFT($I1273,11)="GenFollower",IF(INDEX(rgCurveSpreadORides,MATCH($J1273,rgCurveIDs,0),MATCH(AW$9,rgCurveSpreadORidesColHeaders,0))="",((SUMIF(rgCurveIDs,$J1273,OFFSET(rgCurveSpreads,0,MATCH(BU$9,rgCurveSpreadsColHeaders,0)-1,ROWS(rgCurveSpreads),1))/(COUNTIF(rgCurveIDs,$J1273)-1))*INDEX(rgCurveScaleFactors,MATCH($J1273,rgCurveIDs,0),MATCH(BU$11,rgCurveScaleFactorsColHeaders,0))+INDEX(rgCurveTwea kFactors,MATCH($J1273,rgCurveIDs,0),MATCH(BU$11,rgCurveTweakFactorsColHeaders,0)))*V1273+AC1273,INDEX(rgCurveSpreadORide s,MATCH($J1273,rgCurveIDs,0),MATCH(AW$9,rgCurveSpreadORidesColHeaders,0))*V1273+$AC1273),(SUMIF(rgCurveIDs,$O1273,OFFSET (rgCurveSpreads,0,MATCH(BU$9,rgCurveSpreadsColHeaders,0)-1,ROWS(rgCurveSpreads),1))/(COUNTIF(rgCurveIDs,$O1273)-1))*V1273+$AC1273))))

lenze · Mar 29th, 2002, 03:22 PM

That's some formula. One way might be to break it up, putting each IF statement in a Different Cell. If you name each Cell, you can rewrite your formula using the named ranges in of each cell. This should allow you to see where the formula gets off the track

Just a thought

[ This Message was edited by: lenze on 2002-03-29 14:23 ]

spg2112 · Mar 29th, 2002, 04:00 PM

Just curious, what does this formula do for you?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 03:11 PM	#1
Ola New Member Join Date: Mar 2002 Posts: 1	Is there any easy way or existing tool that enables you to look at and resolve long complex formulas i.e. =IF($I1273="Bmk",AW1273,IF(ISNUMBER(AJ1273),AJ1273,IF(AND($I1273="GenFollowerDerv",$B1273=1),BI1273,IF(LEFT($I1273,11)="GenFollower",IF(INDEX(rgCurveSpreadORides,MATCH($J1273,rgCurveIDs,0),MATCH(AW$9,rgCurveSpreadORidesColHeaders,0))="",((SUMIF(rgCurveIDs,$J1273,OFFSET(rgCurveSpreads,0,MATCH(BU$9,rgCurveSpreadsColHeaders,0)-1,ROWS(rgCurveSpreads),1))/(COUNTIF(rgCurveIDs,$J1273)-1))INDEX(rgCurveScaleFactors,MATCH($J1273,rgCurveIDs,0),MATCH(BU$11,rgCurveScaleFactorsColHeaders,0))+INDEX(rgCurveTwea kFactors,MATCH($J1273,rgCurveIDs,0),MATCH(BU$11,rgCurveTweakFactorsColHeaders,0)))V1273+AC1273,INDEX(rgCurveSpreadORide s,MATCH($J1273,rgCurveIDs,0),MATCH(AW$9,rgCurveSpreadORidesColHeaders,0))V1273+$AC1273),(SUMIF(rgCurveIDs,$O1273,OFFSET (rgCurveSpreads,0,MATCH(BU$9,rgCurveSpreadsColHeaders,0)-1,ROWS(rgCurveSpreads),1))/(COUNTIF(rgCurveIDs,$O1273)-1))V1273+$AC1273))))

Mar 29th, 2002, 03:22 PM	#2
lenze MrExcel MVP Join Date: Feb 2002 Location: Helena, MT Posts: 13,690	That's some formula. One way might be to break it up, putting each IF statement in a Different Cell. If you name each Cell, you can rewrite your formula using the named ranges in of each cell. This should allow you to see where the formula gets off the track Just a thought [ This Message was edited by: lenze on 2002-03-29 14:23 ]

Mar 29th, 2002, 04:00 PM	#3
spg2112 Board Regular Join Date: Feb 2002 Location: Dallas, TX Posts: 312	Just curious, what does this formula do for you?