Need help with iteration

[Ron M.]

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?

[Gary''s Student]

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

[Ron M.]

Thanks for trying, but your answer makes no sense at all.

Ron M.

[MrShorty]

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?

[Ron M.]

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.

[B. R.Ramachandran]

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

[Gary''s Student]

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

[Gary''s Student]

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