round result of sumif formula

[Lilyput]

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.

 

[T. Valko]

To round to the nearest integer:

=ROUND(your_formula_here,0)

 

[Lilyput]

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

 

[Fred Smith]

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]

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]

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

 

[Joe User]

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 -----

 

[Lilyput]

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 copy>paste special values>formula
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]

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

 

[Joe User]

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 copy>paste special values>formula 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]

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%

 

[Fred Smith]

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]

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]

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