Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If statement w/ rounding but not all numbers
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 |
#2
|
|||
|
|||
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, Im new to excel forums! Im 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, Ive added some functions to do it for me. There is a piece that Im struggling with. Perhaps someone has done something similar and can help me. Heres what Im 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 Heres 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 Ill need to do an if statement that says something like 廬f total < .1 then 彙egin the rounding, else 弸ay! Go to next scenario. Im praying that I dont have to do an array, Im 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 |
#3
|
|||
|
|||
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? -- AMarie ------------------------------------------------------------------------ AMarie's Profile: http://www.excelforum.com/member.php...o&userid=27924 View this thread: http://www.excelforum.com/showthread...hreadid=474763 |
#4
|
|||
|
|||
On Mon, 10 Oct 2005 09:19:49 -0500, 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. :) 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 |
#5
|
|||
|
|||
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. -- AMarie ------------------------------------------------------------------------ AMarie's Profile: http://www.excelforum.com/member.php...o&userid=27924 View this thread: http://www.excelforum.com/showthread...hreadid=474763 |
#6
|
|||
|
|||
On Mon, 10 Oct 2005 13:54:51 -0500, AMarie
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rounding numbers to the nearest 5 or 0 | Excel Worksheet Functions | |||
How do I stop rounding numbers? | Excel Discussion (Misc queries) | |||
Rounding numbers to the nearest thousand | Excel Discussion (Misc queries) | |||
can the negative numbers in an IF statement be in a diff color th. | Excel Worksheet Functions | |||
can the negative numbers in an IF statement be in a diff color th. | Excel Worksheet Functions |