• 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.

Looking to simplify this formula

Koushik Chandra

New Member
Hello,

I have this LONG formula which I have come up with in order to lookup certain criteria on another sheet, to fill out and calculate data in the original sheet.

Is there anyway to simplify this by looking at the given formula?

=IF($I7="Flat",(($J7/12)/10000)*AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE)),IF($I7="Tiered",IF(AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))>($N7+$P7),(($J7/12)/10000)*$N7+(($K7/12)/10000)*$P7+(($M7/12)/10000)*(AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))-$N7-$P7),IF(AND(AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))>$N7,AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))<($N7+$P7)),(($J7/12)/10000)*$N7+(($K7/12)/10000)*(AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))-$N7),IF(AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE))<$N7,(($J7/12)/10000)*AVERAGE(VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,Q$1,FALSE),VLOOKUP($A7,'AUM FC'!$A$8:$BY$304,R$1,FALSE)),0)))))

Many thanks in advance
 
If you apply all the comments you should get
=IF($I7="Flat",($J7/120000)*AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE)),IF($I7="Tiered",IF(AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))>($N7+$P7),($J7/120000)*$N7+($K7/120000)*$P7+(($M7/12)/10000)*(AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))-$N7-$P7),IF(AND(AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))>$N7,AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))<($N7+$P7)),($J7/120000)*$N7+($K7/120000)*(AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))-$N7),IF(AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE))<$N7,($J7/120000)*AVERAGE(VLOOKUP($A7,MyRng,Q$1,FALSE),VLOOKUP($A7,MyRng,R$1,FALSE)),0)))))

where MyRng is a named formula for 'AUM FC'!$A$8:$BY$304
 
If you share the file or a sample file we may be able to assist you with crafting a better solution.
 
Back
Top