Excel help

[mukeshc]

Hi

I am working on some financial data were i am using different method to trend some of the values in a specific column.

i need to specify whatever formula used in the column to derive values to the very next column.

[TABLE="width: 300"]
<tbody>[TR]
[TD] 2001[/TD]
[TD]2002[/TD]
[TD]2003[/TD]
[TD]2004[/TD]
[TD]2005 Derived[/TD]
[TD]Tag[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]23[/TD]
[TD]34[/TD]
[TD]43[/TD]
[TD][TABLE="width: 100"]
<tbody>[TR]
[TD]=65+43-65[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD]if this formula is used tag needed is 2 point trend[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]45[/TD]
[TD]=average(45)\average(x)*Y[/TD]
[TD]tagg needed is historic growth rate[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]45[/TD]
[TD]50[/TD]
[TD]if actual value is there(no formula) tag should be Actual.[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]20[/TD]
[TD]12[/TD]
[TD]25[/TD]
[TD]=x*y*45*z[/TD]
[TD]if any other formula then above ones is used tag schoukd be exceptionl.[/TD]
[/TR]
[TR]
[TD]42[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]if cell is blank then no tagg.[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

PLs help.
thanks for helping in advance.

 

[Yongle]

This method uses =FORMULATEXT which I beleive is only present in Excel 2016

1. Create 2 named ranges HGR & TPT both with RefersTo: =0
2. Amend your formulas for Historic Growth Rate and 2 Point Trend like this
=HGR+(average(45)\average(x)*Y)
=TPT+(65+43-65)
(This adds zero to the result but makes it easy to create the condition)

This formula then gives the correct result
=IF(ISFORMULA(E2),IF(LEFT(FORMULATEXT(E2),4)="=HGR","Historic Growth Rate",IF(LEFT(FORMULATEXT(E2),4)="=TPT","Two Point Trend","Exceptional")),IF(ISBLANK(E2),"No Tag","Actual"))