Excel - round result of sumif formula

Asked By Lilyput

14-Jan-10 08:27 PM

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.

14-Jan-10 09:10 PM

To round to the nearest integer:

=ROUND(your_formula_here,0)

--
Biff
Microsoft Excel MVP

15-Jan-10 03:51 AM

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

15-Jan-10 05:39 AM

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

Regards,
Fred

15-Jan-10 09:45 AM

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?

15-Jan-10 10:56 AM

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

15-Jan-10 11:07 AM

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

15-Jan-10 06:09 PM

I have copied the formula this time and it is entered in my sheet as an array
formula however I do not 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)

15-Jan-10 07:56 PM

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
to round up so I have allocated the monthly target". Show us in an example
how this would work.

Regards,
Fred

15-Jan-10 10:49 PM

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:

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

16-Jan-10 07:41 AM

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%

16-Jan-10 09:11 PM

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

17-Jan-10 04:36 AM

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.

17-Jan-10 06:30 PM

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

Fred