Simple iteration problem

[bstevens]

This is something else I desperately need help with at work, but just
can not figure it out. I THINK it is an iteration problem, but I am not
sure:

Cells A1:A500 contain numbers, all of them 5 or 6 figures.
Cell A501 contains the total of all those numbers, a simple autosum.

Cell B1 contains a number.

The problem:

When you enter a number in B1, all of the numbers in A1:A500 are
increased or decreased by the same amount, so that A501 = B1.

If it helps, these are all positive numbers (dollar amounts), and the
increments and decrements will be very small, maybe a few hundred or a
thousand dollars or so. None of them will fly off into the negatives or
anything like that.

I am thinking of it taking the total of the current numbers and finding
the difference between that and the amount in B1. Then take that
difference and add or subtract it from the numbers in A1:A500...
something like that. But I just can not seem to nail it.

Thanks very much,
Ron M.

 

[bstevens]

Sorry, I forgot to include something. I apologize:

The "adjusted" amounts need to appear in another column. Say the number
in B1 can be in C1 instead. The "adjusted" amounts of the numbers in
A1:A500 need to appear in B1:B500.

Again, I apologize.

Thanks,
Ron M.

 

[Ardus Petus]

In B1, enter:
=A1+($C$1-SUM($A$1:$A$25))/ROWS($A$1:$A$25)

See example: http://cjoint.com/?gks6HbAbgZ

HTH

 

[Guest]

Iteration is not needed. Just scale each entry in A1 thru A500 by the ratio
of B1 to A501:


Sub summit()
v = cells(1, "B").Value / cells(501, "A").Value
For i = 1 To 500
cells(i, "A").Value = cells(i, "A").Value * v
Next
End Sub

 

[Guest]

If you want the results in another column, say column D then:

Sub summit()
v = cells(1, "B").Value / cells(501, "A").Value
For i = 1 To 500
cells(i, "D").Value = cells(i, "A").Value * v
Next
End Sub

and the cells in column A will not be over-written