• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trying to eliminate specific cell references in forecast formula

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!
 
Koushik

Apart from tidying it up why change it if it isn't broken ?

Are you aware of the error on the 3rd line (in Red below):

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

You can simplify some of the maths as below, by replacing /12/10000 by /120000:

=IF($H6="Flat",($I6/120000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),IF($H6="Tiered",IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)>($M6+$O6),($I6/120000)*$M6+($J6/120000)*$O6+(#REF!/120000)*(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/120000)*$M6+($J6/120000)*(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)-$M6),IF(AVERAGE('AUM FC'!P6,'AUM FC'!Q6)<$M6,($I6/120000)*AVERAGE('AUM FC'!P6,'AUM FC'!Q6),0)))))

And then simplify ranges like:

=IF($H6="Flat",($I6/120000)*AVERAGE('AUM FC'!P6:Q6),IF($H6="Tiered",IF(AVERAGE('AUM FC'!P6:Q6)>($M6+$O6),($I6/120000)*$M6+($J6/120000)*$O6+(#REF!/120000)*(AVERAGE('AUM FC'!P6:Q6)-$M6-$O6),IF(AND(AVERAGE('AUM FC'!P6:Q6)>$M6,AVERAGE('AUM FC'!P6:Q6)<($M6+$O6)),($I6/120000)*$M6+($J6/120000)*(AVERAGE('AUM FC'!P6:Q6)-$M6),IF(AVERAGE('AUM FC'!P6:Q6)<$M6,($I6/120000)*AVERAGE('AUM FC'!P6:Q6),0)))))
 
Hi Koushik ,

The formula posted is not complex ; it is complicated.

The reason it is complicated is because helper columns have not been used.

The segment :

AVERAGE('AUM FC'!P6,'AUM FC'!Q6)

is used at least 8 times ; if this were put in a helper column , the formula becomes that much easier to read , and probably understand.

In fact , even after this , why combine two situations in one ? Have two additional columns , one of which does the calculcation for Flat , and the other does the calculcation for Tiered.

The final output formula merely has :

=IF($H6="Flat", Cell which has the result for Flat , IF($H6 = "Tiered" , Cell which has the result for Tiered , Default Output)

In fact , a basic tenet of good formula development is to avoid unnecessary parentheses , since they make it very difficult to recognize where one segment ends and another begins.

A simple construct such as :

(($I6/12)/10000)

has 4 unnecessary parentheses.

Narayan
 
Thank You for the feedback! Hui, that simplification of ranges definitely helps, however I'm trying to eliminate the Specific cell references altogether since the data dump may not always be formatted to that type of cell structure. Also, Narayan ... How am I able to use helper columns if I have almost 40 or so desired results columns? Specific Example is, I have starting results in column starting 01/31/2012 and a corresponding column for each month all the way to 06/30/2016 currently. Do I have to make a helper column for EACH specific month or is there a way to set this up so I only need the first two, and then have the results flood through given specific parameters/dates?

All feedback is genuinely appreciated .. Thank You
 
Hi ,

If you can upload your workbook with adequate data in it , it may be possible to redesign it completely ; such piece-meal questions are not likely to bring forth the right answers.

Narayan
 
Back
Top