IF formula with asset allocation (and changing weightings & values)

[whobetta]

I am just developing a small asset allocation workbook and want to have it automated to the point where i can change around allocation % and not worry about having to re-do any formulas. Additionally not having to write a fresh formula in each cell the first time would be nice, so having the ability to get it right once then copy to the remaining ones would be helpful. Sadly I am not even sure what formula I am looking for, but currently I'm using some IF ones.

i am hoping to just have the formula's in the cells and if I wind up changing the allocation % then they would auto-correct, going from 1 column to 2 or 2 columns to 1.


=IF($C16<0.0000001,"",IF($C16<0.05,$D16,IF($C16>0.05,SUM($C16/2*$B$6),""))) -

Rows B16 - 24 are different asset categories (Large Value, Large Blend Large Growth, Mid Value etc..)

column "C" is the [Allocation %] while column "D" is the [Allocation $] section. $B$6 is the TOTAL portfolio value

so for the first row (16), Large Value, if the allocation to this category is over 5% i want to have the allocation split between 2 investments (columns E & F). if it is less than 5% I will only use 1 column(E)

the problem is that if i change cell C16 from 8% to 4% E16 & F16 don't adjust accordingly. I would want E16 to STAY as $40,000 but would then see F16 go to zero.

I am currently unsure how to get this to work on a consistent basis and have it apply to the "Conservative" SHEET

really appreciate any help or thoughts with this any better way to do it would be super appreciated as I am an excel novice