Excel - IF formula statement that would spread an annual budget over 12 months

Asked By r_ehrlic on 24-Mar-08 06:29 AM
I'd like to create an IF type formula that will take an annual budget
keyed into say cell A1 and spread it over 12 months based on options
numbers say 1-10

For example, selecting option number 1 would allocate the annual
budget evenly over 12 months, option number 2 would allocated the
annual budget over four quarters, option number 3 would allocate the
budget over the first two months and last two months of the year and
so one.  Each option number would be based on a percentage weighting
laid out on another worksheet within that same workbook.

Is something like this possible?

Any help is greatly appreciated.

Robert




post_a_repl replied on 23-Mar-08 08:07 AM
A cell formula can only act on the cell that contains it.

You could write (possibly nested) IF formulas in each cell, but your post
provided too little information about the sheet layout for specifics.  As a
starting point, a monthly cell might contain something like
=IF(optioncell=1,A1/12,"")

Instead of writing formulas in each cell, another approach would write a VBA
macro that would populate the relevant cells, but that is a more advanced
topic than is likely wanted in a newusers' post.

Jerry