Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
I've been trying to figure out how to use the "iteration" feature. I
have it checked in the preferences, but can't seem to "turn it on" in the actual formula. I'm doing something wrong. The situation is pretty simple: Cell P4 contains the number 1.0001. Cells M4:M500 contain numbers. Cells N4:N500 contain those numbers multiplied by the value in cell P4. Cell N501 contains the sum of N4:N500. I need to increase cell P4 in increments of .0001 until N501 equals 8,800,000, or gets as close as it can without going over. I've tried it using something like, for cell N4: IF($N$501<8800000,N4*$P$4) And it just does it one time and sits there. I've tried playing with Solver and Goal Seek and can't make it work. Can somebody help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
You don't need iteration in any form. Remember that mutiplication is
distributive. Sum column M =SUM(M4:M500) in M501 factor=88000000/M501 Then round factor to the nearest 10 thousandth. -- Gary's Student "Ron M." wrote: I've been trying to figure out how to use the "iteration" feature. I have it checked in the preferences, but can't seem to "turn it on" in the actual formula. I'm doing something wrong. The situation is pretty simple: Cell P4 contains the number 1.0001. Cells M4:M500 contain numbers. Cells N4:N500 contain those numbers multiplied by the value in cell P4. Cell N501 contains the sum of N4:N500. I need to increase cell P4 in increments of .0001 until N501 equals 8,800,000, or gets as close as it can without going over. I've tried it using something like, for cell N4: IF($N$501<8800000,N4*$P$4) And it just does it one time and sits there. I've tried playing with Solver and Goal Seek and can't make it work. Can somebody help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
Thanks for trying, but your answer makes no sense at all.
Ron M. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
The "iteration" option is used when your problem sets up as a "circular reference". It is designed for cases where one would use a numerical method (like successive approximations or a Newton Raphson method) to converge on the correct answer. As you state your problem, it never "converges" to anything, just grows without bound. As Gary's Student said, this is a problem that doesn't need any kind of iteration, it needs algebra. I'll elaborate on his solution: 1) To reduce your description of the problem to algebraic notation: 8.8E6=sum(k*n(i)) 2) As noted, multiplication is distributive, so we can rewrite as 8.8E6=k*sum(n(i)) 3) k is the unkown, so we solve for k: k=8.8e6/sum(n(i)). Then round k down to the nearest 1e-4, and you have your solution. Does that make any more sense? -- MrShorty ------------------------------------------------------------------------ MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181 View this thread: http://www.excelforum.com/showthread...hreadid=521870 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
Hi,
Gary's suggestion is perfect. All you want is a number in P4 such that P4 when multiplied by the total of M4:M500 will yield 8800000. So P4 should be equal to 8800000/SUM(M4:M500). In P4, use the formula =8800000/SUM(M4:M500) Round the resulting number in P4 to the nearest ten-thousandth (since you want to use increments of 0.0001, and 0.0001 is 1/10000) Regards, B. R. Ramachandran "Ron M." wrote: Thanks for trying, but your answer makes no sense at all. Ron M. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
Oh, good grief. This isn't a mathematics forum. It's an Excel forum.
You know.. Excel? The spreadsheet program? Sorry, I have no idea what you're talking about... "round k down to the nearest 1e-4 Newton Raphson blahblahblahwhoopeekipperedherring." I thought iteration simply allowed a formula or calculation to repeat itself until a certain value was reached. In this case, increase P4 in increments of .0001 until N501 = 8,800,000. Normally, when somebody posts a question on this newsgroup, the person replying will suggest a VBA script or tell them what to type in the formula bar, not throw a bunch of propellerhead math mumbo-jumbo at them. Ya'know? Sigh. Ron M. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
Perhaps...
Multiplying each number in N4:N500 by P4 and then adding them up is the same thing as first adding all the N values and then doing a single mutiplication. In either case you know that the answer should be 8,800,000. For example, the the sum of N4:N500 was 8,796,481 then you would know that P4 must be 1.0004 by simple division. I don't see why iteration is necessary?? -- Gary''s Student "Ron M." wrote: Thanks for trying, but your answer makes no sense at all. Ron M. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need help with iteration
put:
=ROUNDDOWN(8800000/SUM(M4:M500),4) in P4 -- Gary's Student "Gary''s Student" wrote: Perhaps... Multiplying each number in N4:N500 by P4 and then adding them up is the same thing as first adding all the N values and then doing a single mutiplication. In either case you know that the answer should be 8,800,000. For example, the the sum of N4:N500 was 8,796,481 then you would know that P4 must be 1.0004 by simple division. I don't see why iteration is necessary?? -- Gary''s Student "Ron M." wrote: Thanks for trying, but your answer makes no sense at all. Ron M. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Iteration | Excel Discussion (Misc queries) | |||
Iteration | Excel Discussion (Misc queries) | |||
Using iteration to solve a two-parameter equation | Excel Discussion (Misc queries) | |||
How do I resolve complex iteration problems in Excel? | Excel Discussion (Misc queries) | |||
Use of iteration function | Excel Worksheet Functions |