Questions about copying a Formula

[Boblink]

Hi, I would appreciate help in copying a formula across a row that includes
the previous cells results. For example, assuming that the formula in cell
A30 is, +A22*A50, cell B30 would be +B22*$A$50+A30, and cell C30, it would be
+C22*$A$50 +$A$30+B30, I would like to do this for 25+ cells and am sure that
there must be an easier way to replicate this formula.
Thanks, Bob

 

[RagDyer]

In A30 start with:

=A22*A50

Then in B30, enter this formula:

=$A$50*INDEX(22:22,COLUMNS($A:B))+SUM($A$30:A30)

And copy across as needed.

 

[Roger Govier]

Hi

Try in A30
=A$22*$A$50
In B30
=B$22*$A$50+SUM($A$22:A$22)
Copy across as required

 

[RagDyer]

I kinda overcomplicated it ... didn't I?<bg>

 

[Roger Govier]

It happens - usually to me<bg>

 

[Boblink]

Thank you for your help RD and Roger, I used the simpler solution but as they
say, in the land of the blind, the one eye giant is king and the
"complicated" solution obvious was MUCH simpler than my approach,so thank you
for your help guys.

If either of you are up to it, I am playing around with an "IF" calculation
that I am having all sorts of trouble with.

Actually, there are 4 different "Ifs" with the following criteria:

* Cells 11, 13, 25 and 31 contain positive numbers.
* Cells 11, 13 and 25 can NOT be negative, cell 31 CAN be negative
* Cell 37 should be subtracted from each of these cells individually in the
following sequence:
- The first calculation is, cell 37 subtracted from cell 11
- Once cell 11 is negative, cell 37 should be subtracted from cell 13
- Once cell 13 is negative, cell 37 should be subtracted from cell 25
- Once cell 25 is negative, cell 37 should be subtracted from cell 31
- If cell 31 is negative, cell 31 CAN contain a negative (if cell <37 less
cell 31> is negative, the negative number should appear in cell 31)
* Cells 11, 13 and 25 can not be negative; they can only contain a positive
number.
*If the results of the calculations are negative (that is cell 37 is greater
than the one of these cells), a “zero†should be entered into this cell and
the negative results should be brought forward to the next cell.

Example:
if B37 less B11 is positive, the positive number should appears in cell 11.
If C37 less C11 is negative, a “zero†should be entered for cell C11 and
the negative results be carried to the next cell in the sequence, C13.
The calculations for C13 would be (C37 less C13) less (C37 less C11).
The calculations for the “D†cells would be:
- Cell D11 is “zeroâ€
- D13 would be the results of (D37 less D13)
This continues until (D37 less D13) is a negative number
When D13 becomes negative, a “zero†should be entered in cell D13 and the
negative results brought to cell D25 (as was done when cell 11 became
negative)
Cells 11 and 13 would be “zero†and the calculations (cell 37 less cell 25)
continue until cell 25 is negative.
Once cell 25 becomes negative, the process is repeated for cell 31
If (cell 37- cell 31) results in a negative number, the NEGATIVE number
SHOULD be displayed in cell 31.
Cell 31 CAN display BOTH positive and negative numbers.

Again, thank you for saving me all the time I would have spent with my
myopic approach, =B$22*$A$50+SUM($A$22:A$22) worked just great.
If you can help with the "Ifs", I would appreciate it, if not, I can
understand that there are bigger, better,.... fish to fry.
Thank you again,
Bob

 

[Roger Govier]

Hi Bob

If I understand the problem correctly, then in cell C11
=MAX(0,B11-B37)
in cell C13
=IF(C11>0,B13,MAX(0,(B11+B13)-B37))
in cell C25
=IF(C13>0,B25,MAX(0,(B11+B13+B25)-B$37))
in cell C31
=IF(C25>0,B31,(B11+B13+B25+B31)-B37)

 

[Boblink]

Thank you for your help Roger, you wrote "if I understand the problem
correctly", what you probably should have said was "does BOB understand the
problem correctly"?
I appreciate your help Roger and don't want you to "do the work for me", I
would like to learn how to use the "If" functions and the "MAX" function (so
that I can do this stuff in the future) so my first question is, do you have
any suggestions where I can find information about using these funtcions?
FYI, I looked at HELP in Excel 2002, which only confused me more (if that's
possible).
Now getting back to my current problem, I did some modifications to my
Spreadsheet before I received your reply and row 11 is row 10 and contains
the following Formula =SUM(D39).
I then copied the results from row 10 to row 11 so that I could apply the
Formula that you suggested =MAX(0,B11-B37) which I also modified slightly
by subtracting the previous years results from the equation
( =MAX(0,C11-C37)-B11 ) but am not coming up with the results that I
expected.
Let me add a little background information so you can better understand what
I am doing (trying to do).
I have a formula for row 10 =SUM(D39) that represents a summary of
various revenue streams (less Taxes) and copied the Net Totals from row 10 to
row 11.
The Formula for line 11 is suppose to represent Revenue Stream #1 LESS
Expenses (for your information. row 40 contains Annual Expenses).
So the calculation for the cells in row 11 represent , this years revenue
from Revenue Stream#1 LESS this years Expenses, and the next cell in the row
would represent Next Years available revenue LESS next years
expenses........
Once revenue from Revenue Stream#1 in row 11 is depleted, the next source of
revenue is row 13 which is Revenue Stream#2.
Since you can not have a negative Revenue Stream, entries for all subsequent
cells in row 11 (after Revenue Stream#1 is depleted), would be "zero" and
the formula for row 13 should be row 13 (Revenue Stream#2 LESS row 40
(Expenses).
And when Revenue Stream#2 from row 13 is depleted, the next source of
revenue is Revenue Stream#3 on row 29 and (as with cell 11 above), all
subsequent cells in row 13 AFTER the Revenue Stream#2 is depleted (negative),
should display "zero". The Formula for the cells in row 29 should be row 29
LESS row 40.
And when Revenue Stream#3 from line 29 is depleted, ,the next (and last)
source of revenue, is Revenue Stream#4 on line 35.
All cells on line 28 after revenue is depleted will display "zero" and the
calculation for the cells in row 35 would be cell 35 LESS cell 40.
Since this is the LAST source of revenue, a negative number (shortfall)
should appear on row 35 which will allow me to see when I "Run out of money"/
have a shortfall, and hopefully, enable me to plan accordingly.
Any help that you can offer would surely be appreciated Roger but just as
important, a source for "self help" would be nice.
Thank you for ALL your help Roger,
Bob



=======================================================
then in cell C11
=MAX(0,B11-B37)
in cell C13
=IF(C11>0,B13,MAX(0,(B11+B13)-B37))
in cell C25
=IF(C13>0,B25,MAX(0,(B11+B13+B25)-B$37))
in cell C31
=IF(C25>0,B31,(B11+B13+B25+B31)-B37)

 

[Roger Govier]

Hi Bob

Send me the file directly, and I will take a look later this afternoon.
To send direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address.

 

[Boblink]

Hi Roger, thank you for your generous offer, I sent you the Spreadsheet
(email address: (e-mail address removed)), along with notes and sincerely
appreciate ALL your help.
Any suggestions for If, Max,.... education?
Thanks,
Bob