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