If statement w/ rounding but not all numbers

[AMarie]

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.

 

[Loris]

Instead of worrying about which individual numbers you need to round, you
could simply add the numbers and use the ROUNDUP function on the sum.
Assuming your numbers are located in cells A1, A2, and A3, the cell
containing the answer would contain the following formula:
=ROUNDUP(SUM(A1:A3),0).

 

[AMarie]

Thank you for responding! Adding all and rounding at the end isn'
going to be an option. Each number is representative of the asse
allocation of an investment fund. So I actually need to make th
determination of which funds to round separately and not round the sum
I'm sorry I didn't mention it before. The formula is going to have t
look at all the funds in the scenario, check to see if the sum is < .1
"Begin rounding the highest number", then check again to see if sum
.1, if so then exit the "loop".

Any other ideas

 

[Ron Rosenfeld]

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

 

[AMarie]

I'm sorry for being unclear,

Perhaps "rounding" is the incorrect term for me to use. In any event,
in a list of the following numbers - .3989, .4603, and .1407 (which
equals .9999) needs to equal .1 (or 100 after I multiply it by 100).

..3989 will be changed to .4 then multiplied by 100 to be 40, .4603 will
remain but the "03" will be truncated off, the same is true for .1407 -
the "07" will be truncated off. .46 and .14 will be multiplied by 100 so
my new numbers would be 40, 46, and 14 which would equal 100.

The excel formula will look at the three numbers, determine which ones
need to be changed for the total to equal 100.

Perhaps I'm analyzing it too deeply but it seems like there will have
to be an array and an if statement and perhaps some looping.

 

[Ron Rosenfeld]

I'm sorry for being unclear,

Perhaps "rounding" is the incorrect term for me to use. In any event,
in a list of the following numbers - .3989, .4603, and .1407 (which
equals .9999) needs to equal .1 (or 100 after I multiply it by 100).

3989 will be changed to .4 then multiplied by 100 to be 40, .4603 will
remain but the "03" will be truncated off, the same is true for .1407 -
the "07" will be truncated off. .46 and .14 will be multiplied by 100 so
my new numbers would be 40, 46, and 14 which would equal 100.

The excel formula will look at the three numbers, determine which ones
need to be changed for the total to equal 100.

Perhaps I'm analyzing it too deeply but it seems like there will have
to be an array and an if statement and perhaps some looping.

Did you try the solution I posted?

Just modify it by multiplying each formula by 100.


--ron