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)

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?

caveman.savant wrote:
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)

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?


An error in your logic, maybe. In the block that includes "next 12
choices" with a "large" choice row, there are 13 non-false
possibilities, to each of which you impose a minimum value of 1, so you
can't possibly sum to 12 in this block. I'd fix that aspect first.

You are right. I've changed the Values from 12 to 20. I still get
uneven results



On Apr 21, 6:22*pm, smartin wrote:
caveman.savant wrote:
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)

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?

An error in your logic, maybe. In the block that includes "next 12
choices" with a "large" choice row, there are 13 non-false
possibilities, to each of which you impose a minimum value of 1, so you
can't possibly sum to 12 in this block. I'd fix that aspect first.

Oops I just realized when I was playing with your worksheet a couple
nights ago a change I made was saved... Sorry, I had no idea I could do
that. I think I changed it back (cell L9 was the only thing I changed),
but you might want to check.

Onward... Ok I think I see what you are trying to do. You want to scale
the values in L2:Q5 so the new sum is equal to some target. But, you do
not want any new value less than 1, and only integer values are allowed.

I think it will be sheer luck if this ever works. Think of this: when
you multiply an integer by n/m you likely get some decimal portion*,
which you are discarding by rounding. Since you are rounding these
little errors will sometimes sum close to zero, but this will not always
be the case. And, since you impose a minimum of 1 in the result, you
introduce a positive bias in the error.

So that's what it comes down to: your requirements cannot be met due to
rounding errors in the math.

*trivial cases excepted, which do not apply here for the most part


caveman.savant wrote:
You are right. I've changed the Values from 12 to 20. I still get
uneven results



On Apr 21, 6:22 pm, smartin wrote:
caveman.savant wrote:
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)
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?
An error in your logic, maybe. In the block that includes "next 12
choices" with a "large" choice row, there are 13 non-false
possibilities, to each of which you impose a minimum value of 1, so you
can't possibly sum to 12 in this block. I'd fix that aspect first.