If you go to
http://spreadsheets.google.com/ccc?k...5oWuh8e2RR7hMQ
You will see my spreadsheet.

What I'm trying to figure out is a simple method of Forecasting Choices.
The formula I use (in cell B9 in this example) is
=IF(B2<>FALSE(),IF(ROUND(((B2/$H$3)*$C$7),0)<1,1,ROUND(((B2/$H$3)*$C$7),0)),0)

It's pretty simple, but works.

What it does is:
Takes a set of observation values, and presents a value for each
choice by multiplying a target total by the percentage of each choice
compared to the original sum total.
Check to see if the observed value is not False (choice is not
available).
Check to see if the presented value is less than 1, if so, make it 1
Generate a presented value if not False and not less than 1
With a Small & Medium choice across the Colors the outcome seems ok.
But once I add in a Large Choice the outcome does not match the
target.


Any one see an error? Maybe another way to do this?
If the formula I create were be translated into a VBA Macro, what would you suggest?