Hi
I have a worksheet that calculates targets for 29 people based on thier
percentage share of customers within an area using s sumif formula. My
problem is that by allocating % share of a monthly target excel calculates to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I can get
excel to round highest numbers in a range up to interger and lowest results
are rounded down?
Hope someone can help.

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on thier
percentage share of customers within an area using s sumif formula. My
problem is that by allocating % share of a monthly target excel calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I can get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.

Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the eight
highest results in the range to round up so I have allocated the monthly
target When I have added the round part to my first column result is 4 not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on thier
percentage share of customers within an area using s sumif formula. My
problem is that by allocating % share of a monthly target excel calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I can get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.

The first thing you can try is to post your formula. Without it we can only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the eight
highest results in the range to round up so I have allocated the monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on thier
percentage share of customers within an area using s sumif formula. My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.

The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the eight
highest results in the range to round up so I have allocated the monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on thier
percentage share of customers within an area using s sumif formula. My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.

Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.

"Fred Smith" wrote:
it looks like an array formula, but there are no braces around it. Don't
type the formula into the message -- copy and paste it from the formula
bar.

I agree with the gist of your comment. But when I copy-and-paste an array
formula from the Formula Bar, the curly braces are not included.


----- original message -----

"Fred Smith" wrote in message
...
Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results
*after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that
results
below 0.5 are ignored and adding up the results now rounded up does
not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result is
4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.

I've copied the formula this time and it is entered in my sheet as an array
formula however I don't seem to be able to copypaste special valuesformula
which is the only way i can thionk of copying it without actually going into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.


.

Getting the formula correct is a start, but we need to understand more of
your application. I suggest you give us an example of what you need. You say
"when my monthly target is 8 I need the eight highest results in the range
to round up so I have allocated the monthly target". Show us in an example
how this would work.

Regards,
Fred

"Lilyput" wrote in message
...

I've copied the formula this time and it is entered in my sheet as an
array
formula however I don't seem to be able to copypaste special
valuesformula
which is the only way i can thionk of copying it without actually going
into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't
type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and
percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results
*after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that
results
below 0.5 are ignored and adding up the results now rounded up does
not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result
is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif
formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and
lowest
results
are rounded down?
Hope someone can help.


.


.


.

Lilyput wrote:
I've copied the formula this time and it is entered in my
sheet as an array formula however I don't seem to be
able to copypaste special valuesformula which is the
only way i can thionk of copying it without actually going
into the formula bar which takes the curly brackets away.

Don't worry about it. Copying from Formula Bar is just fine. You cannot
copy the curly braces, to my knowledge. And if you could, that might result
in scolding from those who might mistakenly think you actually entered the
curly braces manually. It is sufficient for you to say that you entered it
as an array formula, __and__ you see the curly braces in the Formula Bar
(confirmation).


Lilyput wrote previously:
I have added = Round(),0 to my formula but what happens
is that results below 0.5 are ignored and adding up the
results now rounded up does not result in monthly target.

This is a common quantization problem, i.e. the result of rounding "long"
decimal fractions to fewer decimal places (or integers). There are no
perfect solutions.

Consider the following simple example. You have 3 dollar bills, and you
want to award them to 4 people in the proportion to their contributions,
which is 25% each. It can't be done! At least, not fairly. Someone must
get zero.

One simple (and very flawed) approach is to put the following formulas into
A1 and A2, say, and copy A2 down through A29:

A1: =ROUND(Profiles!D$4*'Customer share'!G8),0)

A2: =MIN(Profiles!D$4 - SUM($A$1:A1),
ROUND(Profiles!D$4*'Customer share'!G9),0))

I assume that Profiles!D4 is the target, and 'Customer share'!G8:G36 are the
%shares.

That is approach is flawed because it is more unfair to the people
represented by the later cells.

I have tried other approaches in the past. But I think there is a
worst-case scenario for each one. (I would have to do a search to find them.)

PS: It is unclear to me what you are trying to compute with the
SUM(IF(...)) formula, which might be better written as a non-array SUMPRODUCT
formula.


----- original message -----

"Lilyput" wrote:
I've copied the formula this time and it is entered in my sheet as an array
formula however I don't seem to be able to copypaste special valuesformula
which is the only way i can thionk of copying it without actually going into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.


.

First can I just thank you all for your patience and quick rtesponses? I am
not an excel expert and have prety much taught my self and therefor may not
do things the best way. Please excuse my ignorance and inability to clearly
explain what i want to do!

The reason i used sum if is because to me that was the best way I could see
to get formula to look at customer share and find the % share to multiply by
the monthly profile depending on the name in the profiles sheet.

However this part is not the rpoblem, my problem is that having given each
name a share of the target which is shown at A1 below I need results in
B2:B30 to add up to 8 in B31 not 4 - but as i cannot allocate less than 1 as
a target to a person how do I get excel to give me results without me having
to go in and add number manualy? EG - give name 4 a target of one etc? I know
that maybe does not look fair but i must allocate all of the 8 - which
represents people so someone cannot achive less than 1 - does this make any
sense at all?
A B C
Staff 8 % share
Name 1 2 27.9%
Name 2 0 3.0%
Name 3 0 1.5%
Name 4 0 2.3%
Name 5 0 1.8%
Name 6 0 1.0%
Name 7 0 5.4%
Name 8 0 1.4%
Name 9 0 2.1%
Name 10 0 1.4%
Name 11 0 3.6%
Name 12 0 0.9%
Name 13 0 1.7%
Name 14 0 2.0%
Name 15 0 1.6%
Name 16 0 1.9%
Name 17 1 10.5%
Name 18 0 3.7%
Name 19 0 1.1%
Name 20 0 1.4%
Name 21 0 2.1%
Name 22 1 9.5%
Name 23 0 2.8%
Name 24 0 0.8%
Name 25 0 2.9%
Name 26 0 0.6%
Name 27 0 2.0%
Name 28 0 2.7%
Total Name4 100.0%



"Joe User" wrote:

Lilyput wrote:
I've copied the formula this time and it is entered in my
sheet as an array formula however I don't seem to be
able to copypaste special valuesformula which is the
only way i can thionk of copying it without actually going
into the formula bar which takes the curly brackets away.

Don't worry about it. Copying from Formula Bar is just fine. You cannot
copy the curly braces, to my knowledge. And if you could, that might result
in scolding from those who might mistakenly think you actually entered the
curly braces manually. It is sufficient for you to say that you entered it
as an array formula, __and__ you see the curly braces in the Formula Bar
(confirmation).


Lilyput wrote previously:
I have added = Round(),0 to my formula but what happens
is that results below 0.5 are ignored and adding up the
results now rounded up does not result in monthly target.

This is a common quantization problem, i.e. the result of rounding "long"
decimal fractions to fewer decimal places (or integers). There are no
perfect solutions.

Consider the following simple example. You have 3 dollar bills, and you
want to award them to 4 people in the proportion to their contributions,
which is 25% each. It can't be done! At least, not fairly. Someone must
get zero.

One simple (and very flawed) approach is to put the following formulas into
A1 and A2, say, and copy A2 down through A29:

A1: =ROUND(Profiles!D$4*'Customer share'!G8),0)

A2: =MIN(Profiles!D$4 - SUM($A$1:A1),
ROUND(Profiles!D$4*'Customer share'!G9),0))

I assume that Profiles!D4 is the target, and 'Customer share'!G8:G36 are the
%shares.

That is approach is flawed because it is more unfair to the people
represented by the later cells.

I have tried other approaches in the past. But I think there is a
worst-case scenario for each one. (I would have to do a search to find them.)

PS: It is unclear to me what you are trying to compute with the
SUM(IF(...)) formula, which might be better written as a non-array SUMPRODUCT
formula.


----- original message -----

"Lilyput" wrote:
I've copied the formula this time and it is entered in my sheet as an array
formula however I don't seem to be able to copypaste special valuesformula
which is the only way i can thionk of copying it without actually going into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded by
apostrophes, I would have expected Excel to give you an error. Also, it
looks like an array formula, but there are no braces around it. Don't type
the formula into the message -- copy and paste it from the formula bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we can
only
guess. From what you've provided, why don't you round the results *after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that results
below 0.5 are ignored and adding up the results now rounded up does not
result in monthly target. So when my monthly target is 8 I need the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people based on
thier
percentage share of customers within an area using s sumif formula.
My
problem is that by allocating % share of a monthly target excel
calculates
to
decimal points and i then have to overtype results so they add up
particularly where monthly targets are low. Is there a way that I
can
get
excel to round highest numbers in a range up to interger and lowest
results
are rounded down?
Hope someone can help.


.


.


.

Let's continue on with your example. I assume that "Name 1" is in column A
(it would have been better if there was no space in the example, as it looks
like the 1 is in a separate column). Is 8 in D4? Is that why D4 is part of
your formula? Your formula also refers to column G, but your example shows
column C. Are these supposed to be the same, or are they different data?

You want to allocate 8 in cells b2:b30, based on the percentage in column C.
How do you do it manually? Given this data how should the 8 be distributed?

Regards,
Fred

"Lilyput" wrote in message
...
First can I just thank you all for your patience and quick rtesponses? I
am
not an excel expert and have prety much taught my self and therefor may
not
do things the best way. Please excuse my ignorance and inability to
clearly
explain what i want to do!

The reason i used sum if is because to me that was the best way I could
see
to get formula to look at customer share and find the % share to multiply
by
the monthly profile depending on the name in the profiles sheet.

However this part is not the rpoblem, my problem is that having given each
name a share of the target which is shown at A1 below I need results in
B2:B30 to add up to 8 in B31 not 4 - but as i cannot allocate less than 1
as
a target to a person how do I get excel to give me results without me
having
to go in and add number manualy? EG - give name 4 a target of one etc? I
know
that maybe does not look fair but i must allocate all of the 8 - which
represents people so someone cannot achive less than 1 - does this make
any
sense at all?
A B C
Staff 8 % share
Name 1 2 27.9%
Name 2 0 3.0%
Name 3 0 1.5%
Name 4 0 2.3%
Name 5 0 1.8%
Name 6 0 1.0%
Name 7 0 5.4%
Name 8 0 1.4%
Name 9 0 2.1%
Name 10 0 1.4%
Name 11 0 3.6%
Name 12 0 0.9%
Name 13 0 1.7%
Name 14 0 2.0%
Name 15 0 1.6%
Name 16 0 1.9%
Name 17 1 10.5%
Name 18 0 3.7%
Name 19 0 1.1%
Name 20 0 1.4%
Name 21 0 2.1%
Name 22 1 9.5%
Name 23 0 2.8%
Name 24 0 0.8%
Name 25 0 2.9%
Name 26 0 0.6%
Name 27 0 2.0%
Name 28 0 2.7%
Total Name4 100.0%



"Joe User" wrote:

Lilyput wrote:
I've copied the formula this time and it is entered in my
sheet as an array formula however I don't seem to be
able to copypaste special valuesformula which is the
only way i can thionk of copying it without actually going
into the formula bar which takes the curly brackets away.

Don't worry about it. Copying from Formula Bar is just fine. You cannot
copy the curly braces, to my knowledge. And if you could, that might
result
in scolding from those who might mistakenly think you actually entered
the
curly braces manually. It is sufficient for you to say that you entered
it
as an array formula, __and__ you see the curly braces in the Formula Bar
(confirmation).


Lilyput wrote previously:
I have added = Round(),0 to my formula but what happens
is that results below 0.5 are ignored and adding up the
results now rounded up does not result in monthly target.

This is a common quantization problem, i.e. the result of rounding "long"
decimal fractions to fewer decimal places (or integers). There are no
perfect solutions.

Consider the following simple example. You have 3 dollar bills, and you
want to award them to 4 people in the proportion to their contributions,
which is 25% each. It can't be done! At least, not fairly. Someone
must
get zero.

One simple (and very flawed) approach is to put the following formulas
into
A1 and A2, say, and copy A2 down through A29:

A1: =ROUND(Profiles!D$4*'Customer share'!G8),0)

A2: =MIN(Profiles!D$4 - SUM($A$1:A1),
ROUND(Profiles!D$4*'Customer share'!G9),0))

I assume that Profiles!D4 is the target, and 'Customer share'!G8:G36 are
the
%shares.

That is approach is flawed because it is more unfair to the people
represented by the later cells.

I have tried other approaches in the past. But I think there is a
worst-case scenario for each one. (I would have to do a search to find
them.)

PS: It is unclear to me what you are trying to compute with the
SUM(IF(...)) formula, which might be better written as a non-array
SUMPRODUCT
formula.


----- original message -----

"Lilyput" wrote:
I've copied the formula this time and it is entered in my sheet as an
array
formula however I don't seem to be able to copypaste special
valuesformula
which is the only way i can thionk of copying it without actually going
into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded
by
apostrophes, I would have expected Excel to give you an error. Also,
it
looks like an array formula, but there are no braces around it. Don't
type
the formula into the message -- copy and paste it from the formula
bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and
percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we
can
only
guess. From what you've provided, why don't you round the results
*after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that
results
below 0.5 are ignored and adding up the results now rounded up
does not
result in monthly target. So when my monthly target is 8 I need
the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column
result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people
based on
thier
percentage share of customers within an area using s sumif
formula.
My
problem is that by allocating % share of a monthly target
excel
calculates
to
decimal points and i then have to overtype results so they
add up
particularly where monthly targets are low. Is there a way
that I
can
get
excel to round highest numbers in a range up to interger and
lowest
results
are rounded down?
Hope someone can help.


.


.


.

Is there anyway I can send you the worksheet? I am not sure how to copy the
example so it makes sesne otherwise? And when I talk about having to enter
results manually what I mean is before I added the rounding to the formula I
could see results to 2 decimal places and rounded the largest ones up to 1 so
I gou a total of 8 and dleted some of the smaller ones.

Any way 8 was originaly in D4 and column c was originally in a separate
sheet in col G called customer share.


"Fred Smith" wrote:

Let's continue on with your example. I assume that "Name 1" is in column A
(it would have been better if there was no space in the example, as it looks
like the 1 is in a separate column). Is 8 in D4? Is that why D4 is part of
your formula? Your formula also refers to column G, but your example shows
column C. Are these supposed to be the same, or are they different data?

You want to allocate 8 in cells b2:b30, based on the percentage in column C.
How do you do it manually? Given this data how should the 8 be distributed?

Regards,
Fred

"Lilyput" wrote in message
...
First can I just thank you all for your patience and quick rtesponses? I
am
not an excel expert and have prety much taught my self and therefor may
not
do things the best way. Please excuse my ignorance and inability to
clearly
explain what i want to do!

The reason i used sum if is because to me that was the best way I could
see
to get formula to look at customer share and find the % share to multiply
by
the monthly profile depending on the name in the profiles sheet.

However this part is not the rpoblem, my problem is that having given each
name a share of the target which is shown at A1 below I need results in
B2:B30 to add up to 8 in B31 not 4 - but as i cannot allocate less than 1
as
a target to a person how do I get excel to give me results without me
having
to go in and add number manualy? EG - give name 4 a target of one etc? I
know
that maybe does not look fair but i must allocate all of the 8 - which
represents people so someone cannot achive less than 1 - does this make
any
sense at all?
A B C
Staff 8 % share
Name 1 2 27.9%
Name 2 0 3.0%
Name 3 0 1.5%
Name 4 0 2.3%
Name 5 0 1.8%
Name 6 0 1.0%
Name 7 0 5.4%
Name 8 0 1.4%
Name 9 0 2.1%
Name 10 0 1.4%
Name 11 0 3.6%
Name 12 0 0.9%
Name 13 0 1.7%
Name 14 0 2.0%
Name 15 0 1.6%
Name 16 0 1.9%
Name 17 1 10.5%
Name 18 0 3.7%
Name 19 0 1.1%
Name 20 0 1.4%
Name 21 0 2.1%
Name 22 1 9.5%
Name 23 0 2.8%
Name 24 0 0.8%
Name 25 0 2.9%
Name 26 0 0.6%
Name 27 0 2.0%
Name 28 0 2.7%
Total Name4 100.0%



"Joe User" wrote:

Lilyput wrote:
I've copied the formula this time and it is entered in my
sheet as an array formula however I don't seem to be
able to copypaste special valuesformula which is the
only way i can thionk of copying it without actually going
into the formula bar which takes the curly brackets away.

Don't worry about it. Copying from Formula Bar is just fine. You cannot
copy the curly braces, to my knowledge. And if you could, that might
result
in scolding from those who might mistakenly think you actually entered
the
curly braces manually. It is sufficient for you to say that you entered
it
as an array formula, __and__ you see the curly braces in the Formula Bar
(confirmation).


Lilyput wrote previously:
I have added = Round(),0 to my formula but what happens
is that results below 0.5 are ignored and adding up the
results now rounded up does not result in monthly target.

This is a common quantization problem, i.e. the result of rounding "long"
decimal fractions to fewer decimal places (or integers). There are no
perfect solutions.

Consider the following simple example. You have 3 dollar bills, and you
want to award them to 4 people in the proportion to their contributions,
which is 25% each. It can't be done! At least, not fairly. Someone
must
get zero.

One simple (and very flawed) approach is to put the following formulas
into
A1 and A2, say, and copy A2 down through A29:

A1: =ROUND(Profiles!D$4*'Customer share'!G8),0)

A2: =MIN(Profiles!D$4 - SUM($A$1:A1),
ROUND(Profiles!D$4*'Customer share'!G9),0))

I assume that Profiles!D4 is the target, and 'Customer share'!G8:G36 are
the
%shares.

That is approach is flawed because it is more unfair to the people
represented by the later cells.

I have tried other approaches in the past. But I think there is a
worst-case scenario for each one. (I would have to do a search to find
them.)

PS: It is unclear to me what you are trying to compute with the
SUM(IF(...)) formula, which might be better written as a non-array
SUMPRODUCT
formula.


----- original message -----

"Lilyput" wrote:
I've copied the formula this time and it is entered in my sheet as an
array
formula however I don't seem to be able to copypaste special
valuesformula
which is the only way i can thionk of copying it without actually going
into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share" surrounded
by
apostrophes, I would have expected Excel to give you an error. Also,
it
looks like an array formula, but there are no braces around it. Don't
type
the formula into the message -- copy and paste it from the formula
bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and
percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it we
can
only
guess. From what you've provided, why don't you round the results
*after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is that
results
below 0.5 are ignored and adding up the results now rounded up
does not
result in monthly target. So when my monthly target is 8 I need
the
eight
highest results in the range to round up so I have allocated the
monthly
target When I have added the round part to my first column
result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in message
...
Hi
I have a worksheet that calculates targets for 29 people
based on
thier
percentage share of customers within an area using s sumif
formula.
My
problem is that by allocating % share of a monthly target
excel
calculates
to
decimal points and i then have to overtype results so they
add up
particularly where monthly targets are low. Is there a way
that I
can
get
excel to round highest numbers in a range up to interger and
lowest
results
are rounded down?
Hope someone can help.


.


.


.


.

Sure. Send it to fred dot smith at shaw dot ca. In the meantime, I'll see
what formula I can come up with that does what you want.

Fred

"Lilyput" wrote in message
...
Is there anyway I can send you the worksheet? I am not sure how to copy
the
example so it makes sesne otherwise? And when I talk about having to enter
results manually what I mean is before I added the rounding to the formula
I
could see results to 2 decimal places and rounded the largest ones up to 1
so
I gou a total of 8 and dleted some of the smaller ones.

Any way 8 was originaly in D4 and column c was originally in a separate
sheet in col G called customer share.


"Fred Smith" wrote:

Let's continue on with your example. I assume that "Name 1" is in column
A
(it would have been better if there was no space in the example, as it
looks
like the 1 is in a separate column). Is 8 in D4? Is that why D4 is part
of
your formula? Your formula also refers to column G, but your example
shows
column C. Are these supposed to be the same, or are they different data?

You want to allocate 8 in cells b2:b30, based on the percentage in column
C.
How do you do it manually? Given this data how should the 8 be
distributed?

Regards,
Fred

"Lilyput" wrote in message
...
First can I just thank you all for your patience and quick rtesponses?
I
am
not an excel expert and have prety much taught my self and therefor may
not
do things the best way. Please excuse my ignorance and inability to
clearly
explain what i want to do!

The reason i used sum if is because to me that was the best way I could
see
to get formula to look at customer share and find the % share to
multiply
by
the monthly profile depending on the name in the profiles sheet.

However this part is not the rpoblem, my problem is that having given
each
name a share of the target which is shown at A1 below I need results in
B2:B30 to add up to 8 in B31 not 4 - but as i cannot allocate less than
1
as
a target to a person how do I get excel to give me results without me
having
to go in and add number manualy? EG - give name 4 a target of one etc?
I
know
that maybe does not look fair but i must allocate all of the 8 - which
represents people so someone cannot achive less than 1 - does this make
any
sense at all?
A B C
Staff 8 % share
Name 1 2 27.9%
Name 2 0 3.0%
Name 3 0 1.5%
Name 4 0 2.3%
Name 5 0 1.8%
Name 6 0 1.0%
Name 7 0 5.4%
Name 8 0 1.4%
Name 9 0 2.1%
Name 10 0 1.4%
Name 11 0 3.6%
Name 12 0 0.9%
Name 13 0 1.7%
Name 14 0 2.0%
Name 15 0 1.6%
Name 16 0 1.9%
Name 17 1 10.5%
Name 18 0 3.7%
Name 19 0 1.1%
Name 20 0 1.4%
Name 21 0 2.1%
Name 22 1 9.5%
Name 23 0 2.8%
Name 24 0 0.8%
Name 25 0 2.9%
Name 26 0 0.6%
Name 27 0 2.0%
Name 28 0 2.7%
Total Name4 100.0%



"Joe User" wrote:

Lilyput wrote:
I've copied the formula this time and it is entered in my
sheet as an array formula however I don't seem to be
able to copypaste special valuesformula which is the
only way i can thionk of copying it without actually going
into the formula bar which takes the curly brackets away.

Don't worry about it. Copying from Formula Bar is just fine. You
cannot
copy the curly braces, to my knowledge. And if you could, that might
result
in scolding from those who might mistakenly think you actually entered
the
curly braces manually. It is sufficient for you to say that you
entered
it
as an array formula, __and__ you see the curly braces in the Formula
Bar
(confirmation).


Lilyput wrote previously:
I have added = Round(),0 to my formula but what happens
is that results below 0.5 are ignored and adding up the
results now rounded up does not result in monthly target.

This is a common quantization problem, i.e. the result of rounding
"long"
decimal fractions to fewer decimal places (or integers). There are no
perfect solutions.

Consider the following simple example. You have 3 dollar bills, and
you
want to award them to 4 people in the proportion to their
contributions,
which is 25% each. It can't be done! At least, not fairly. Someone
must
get zero.

One simple (and very flawed) approach is to put the following formulas
into
A1 and A2, say, and copy A2 down through A29:

A1: =ROUND(Profiles!D$4*'Customer share'!G8),0)

A2: =MIN(Profiles!D$4 - SUM($A$1:A1),
ROUND(Profiles!D$4*'Customer share'!G9),0))

I assume that Profiles!D4 is the target, and 'Customer share'!G8:G36
are
the
%shares.

That is approach is flawed because it is more unfair to the people
represented by the later cells.

I have tried other approaches in the past. But I think there is a
worst-case scenario for each one. (I would have to do a search to
find
them.)

PS: It is unclear to me what you are trying to compute with the
SUM(IF(...)) formula, which might be better written as a non-array
SUMPRODUCT
formula.


----- original message -----

"Lilyput" wrote:
I've copied the formula this time and it is entered in my sheet as
an
array
formula however I don't seem to be able to copypaste special
valuesformula
which is the only way i can thionk of copying it without actually
going
into
the formula bar which takes the curly brackets away.


=ROUND(SUM(IF($A6='Customer share'!$A$8:$A$36,Profiles!D$4*'Customer
share'!$G$8:$G$36)),0)


"Fred Smith" wrote:

Are you sure this is the formula? Without "Customer share"
surrounded
by
apostrophes, I would have expected Excel to give you an error.
Also,
it
looks like an array formula, but there are no braces around it.
Don't
type
the formula into the message -- copy and paste it from the formula
bar.

Regards,
Fred

"Lilyput" wrote in message
...
The formula I have used is
=ROUND(SUM(IF($A5=Customer
share!$A$8:$A$36,'Profile'!D$4*'Customer
share'!$G$8:$G$36)),0)
Col A in profile sheet has names, D$4 has monthly targets and
percentage
share is taken from customer share sheet col G. Does this help?
I am not sure how to adjust my formula to round after
calculation?

"Fred Smith" wrote:

The first thing you can try is to post your formula. Without it
we
can
only
guess. From what you've provided, why don't you round the
results
*after*
you've added them up, rather than before?

Regards,
Fred

"Lilyput" wrote in message
...
Thanks Biff
I have added = Round(),0 to my formula but what happens is
that
results
below 0.5 are ignored and adding up the results now rounded
up
does not
result in monthly target. So when my monthly target is 8 I
need
the
eight
highest results in the range to round up so I have allocated
the
monthly
target When I have added the round part to my first column
result is 4
not 8.
Any thing else I can try?

Lilyput



"T. Valko" wrote:

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP


"Lilyput" wrote in
message
...
Hi
I have a worksheet that calculates targets for 29 people
based on
thier
percentage share of customers within an area using s sumif
formula.
My
problem is that by allocating % share of a monthly target
excel
calculates
to
decimal points and i then have to overtype results so they
add up
particularly where monthly targets are low. Is there a way
that I
can
get
excel to round highest numbers in a range up to interger
and
lowest
results
are rounded down?
Hope someone can help.


.


.


.


.