If statement w/ rounding but not all numbers

[AMarie]

Hello everyone,

I知 new to excel forums! I知 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致e added some functions to do it for me. There is a piece that I知 struggling with. Perhaps someone has done something similar and can help me.

Here痴 what I知 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痴 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値l need to do an if statement that says something like 的f total <> .1 then 澱egin the rounding�, else 添ay! Go to next scenario�.

I知 praying that I don稚 have to do an array, I知 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" wrote:

>
> Hello everyone,
>
> I窶冦 new to excel forums! I窶冦 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窶况e added some functions to do
> it for me. There is a piece that I窶冦 struggling with. Perhaps someone
> has done something similar and can help me.
>
> Here窶冱 what I窶冦 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窶冱 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窶冤l need to do an if
> statement that says something like 窶廬f total <> .1 then 窶彙egin the
> rounding窶�, else 窶弸ay! Go to next scenario窶�.
>
> I窶冦 praying that I don窶冲 have to do an array, I窶冦 an ex-developer and
> me and arrays never got along.
>
> Thanks everyone for reading and please help if you can.
>
>
>
> --
> AMarie
> ------------------------------------------------------------------------
> AMarie's Profile: http://www.excelforum.com/member.php...o&userid=27924
> View this thread: http://www.excelforum.com/showthread...hreadid=474763
>
>

[AMarie]

Thank you for responding! Adding all and rounding at the end isn't going to be an option. Each number is representative of the asset allocation of an investment fund. So I actually need to make the 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 to 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]

On Mon, 10 Oct 2005 09:19:49 -0500, AMarie
<[email protected]> wrote:

>
>Hello everyone,
>
>I知 new to excel forums! I知 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致e added some functions to do
>it for me. There is a piece that I知 struggling with. Perhaps someone
>has done something similar and can help me.
>
>Here痴 what I知 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痴 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値l need to do an if
>statement that says something like 的f total <> .1 then 澱egin the
>rounding�, else 添ay! Go to next scenario�.
>
>I知 praying that I don稚 have to do an array, I知 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]

On Mon, 10 Oct 2005 13:54:51 -0500, AMarie
<[email protected]> wrote:

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