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.

2001	2002	2003	2004	2005 Derived	Tag
10	23	34	43	=65+43-65 <tbody> </tbody>	if this formula is used tag needed is 2 point trend
11	20	30	45	=average(45)\average(x)*Y	tagg needed is historic growth rate
11	20	30	45	50	if actual value is there(no formula) tag should be Actual.
23	20	12	25	=x*y*45*z	if any other formula then above ones is used tag schoukd be exceptionl.
42				if cell is blank then no tagg.

<tbody>
</tbody>

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"))