On Mon, 10 Oct 2005 09:19:49 -0500, AMarie
wrote:


Hello everyone,

I’m new to excel forums! I’m a Quality Analyst for a financial company
and am using excels built in functions to create test scenarios for the
developers to use (they do test driven development). Instead of doing
all my totals and calculations my hand, I’ve added some functions to do
it for me. There is a piece that I’m struggling with. Perhaps someone
has done something similar and can help me.

Here’s what I’m trying to do:

3989
4603
1407
='s
9999

Based on this list (minus the .9999) I need to determine which numbers
I can round up to the tenths place so that my total is .1 instead of
9999. So the numbers above would need to change to:

40
46
14
='s
100

I need these to be whole numbers so I would multiply all by 100 so my
ending totals would be:

40
46
14
='s
100

Here’s the catch, I only round when my total does not equal .1 (or
100). In this case my total equaled .1 after rounding up only one
number (.3989). In some cases, I might need to round two numbers or
perhaps four and so on…

So how should I approach this? I know that I’ll need to do an if
statement that says something like “If total < .1 then “begin the
rounding”, else “Yay! Go to next scenario”.

I’m praying that I don’t have to do an array, I’m an ex-developer and
me and arrays never got along.

Thanks everyone for reading and please help if you can.
:)

Your wording and examples are imprecise. There is obviously no way that any
rounding can result in the sum of a series of integers being 0.1.

In addition, "round up" means to round to the next number away from zero
(higher if positive, lower if negative). In your example, you are only doing
that with your first entry. With the others you are rounding down.

It seems as if you want to ROUND (and not ROUND UP); that your numbers should
have a leading decimal; and that you would want the total of these numbers to
equal one (1).

How are these numbers derived?

It seems as if the simplest thing to "make them add up to 1") would be to ROUND
the calculations for all except the largest of the entries, and then subtract
that sum from 1 to get the percentage for the largest entry.

So if your range of entries (A1:An) is named "rng", then

B1: =ROUND(IF(A1=MAX(rng),1-SUM(rng)+A1,A1),2)

copy/drag down to Bn

Format the SUM as percent.

The Excel ROUND function uses arithmetic rounding, which should be OK for
testing. If you need to use Banker's rounding the formula would be somewhat
different, but the principal would be the same.




--ron