Koushik Chandra
New Member
Hello,
I am trying to forecast revenue for a project I am working on, and am currently using this specific formula:
=IF($H6="Flat",(($I6/12)/10000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),IF($H6="Tiered",IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)>($M6+$O6),(($I6/12)/10000)*$M6+(($J6/12)/10000)*$O6+((#REF!/12)/10000)*(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)-$M6-$O6),IF(AND(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)>$M6,AVERAGE('AUM FC'!P6,'AUM FC'!Q6)<($M6+$O6)),(($I6/12)/10000)*$M6+(($J6/12)/10000)*(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)-$M6),IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)<$M6,(($I6/12)/10000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),0)))))
The 'AUM FC' is a separate tab which has other calculations. Is there anyway I can use a general function to help match my criteria instead of referencing specific cells in order to eliminate error and the complexity of this formula?
Would VBA or a Macro be an easier fix, and if so; what would that certain solution look like, or how would I be able to make it?
Thanks in advance!
I am trying to forecast revenue for a project I am working on, and am currently using this specific formula:
=IF($H6="Flat",(($I6/12)/10000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),IF($H6="Tiered",IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)>($M6+$O6),(($I6/12)/10000)*$M6+(($J6/12)/10000)*$O6+((#REF!/12)/10000)*(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)-$M6-$O6),IF(AND(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)>$M6,AVERAGE('AUM FC'!P6,'AUM FC'!Q6)<($M6+$O6)),(($I6/12)/10000)*$M6+(($J6/12)/10000)*(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)-$M6),IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)<$M6,(($I6/12)/10000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),0)))))
The 'AUM FC' is a separate tab which has other calculations. Is there anyway I can use a general function to help match my criteria instead of referencing specific cells in order to eliminate error and the complexity of this formula?
Would VBA or a Macro be an easier fix, and if so; what would that certain solution look like, or how would I be able to make it?
Thanks in advance!