Excel Autofill Question

[Madmick]

Simple question about autofill. In the above chart, I have a very simply accounting sheet set up. As you can see, the first row with values is the third row. So cell E3= C3-A3. But that's only because it's the first. For every cell after there is a pattern predicated on the total that came before it:
E4= E3 + (C4-A4)
E5= E4 + (C5-A5)
etc.

Now, I can extend this pattern for automatic calculation by grabbing the bottom right square of E4 or below in the E column, and dragging it down. I did this so you could see the result in the sheet. You see $90.00 is the ultimate balance, and then it is repeated again and again since all the expense/profit below that are empty values.

So the problem is that I don't want to have to do that. I want to autofill according to that pattern in perpetuity down the E column because one never knows how many entries might be reached. This is essential.
I'd also prefer it if nothing showed in those cells until there was a balance to be shown (i.e. I don't want "$0.00" all the way down cluttering the column- just the ultimate balance so far). This isn't essential, but strongly desired.

Double-clicking the bottom right of E5 doesn't work. None of the autofill options worked the way I used them. Help, please.

 

[WhoBeDaPlaya]

Would a VBA macro do it?

 

[Madmick]

Would a VBA macro do it?

I don't know what that is. Had to Google it. I'm self-taught on Excel for simple functions. Was putting this together for a business friend.

 

[Binky]

E4 =

IF(SUM(A4:D4)=0,"",E3 + (C4-A4))

Copy that down as far as you want. It will just show blank if there is nothing in the data cells. I use "" to mean nothing. Don't use zero in these functions, since zero is a number. The "" won't screw up other functions like average, median, etc.

 

[Madmick]

E4 =

IF(SUM(A4:D4)=0,"",E3 + (C4-A4))

Copy that down as far as you want. It will just show blank if there is nothing in the data cells. I use "" to mean nothing. Don't use zero in these functions, since zero is a number. The "" won't screw up other functions like average, median, etc.

Awesome, that's definitely what I'm after. Thank you.

But is there no way to tell Excel to continue a formula pattern like this down the column in perpetuity? I'm as much curious because I think understanding how to do this would be really useful in a lot of situations besides just this one. That way if the list goes out to hundreds of items it doesn't depend on the user understanding how to drag down to repeat the E-column pattern; the E-Column will continue it forever down the column itself.

 

[jolancer]

you can use array formula's to cover an entire column without copying down, by selecting a column (A:A) for example, inputing an array formula into the function bar and hitting (ctrl+shit+enter), not advisable though depending on the length or amount of data and computer being used, as excel will bog down even on fast computers if Arrays or Volitile functions are used on very large amounts of data.

A list will automatically grow like what you asked, /highlight/rightclick/create list ,i never used one though so dunno if it suffers the same as arrays or volitile functions, and appears it requires you not to skip an line otherwise it will not grow dynamically

for data you know the limit of how big the list will grow, arrays and volitile functions are safe to use. the most practical way tho is to use a macro ref to your particular question, if formatted correctly macro's don't suffer any of the performance pitfalls of arrays and volitile functions.

If there is going to be a blank in both columns at any point it apears you'll have to do something else for it not to fail, like this for example:

=IF($A4+$C4,SUM($C$3:INDEX($C:$C,ROW()))-SUM($A$3:INDEX($A:$A,ROW())),"")

you could create a smaller function to do the same as above using =SUMPRODUCT() however that would be volitile

i don't use excel for work or anything only personal use, so i don't know if theres a reason for the layout of your sheet. but for the E4=FUNCTION() it would definitly be simpler if the sheet layout was different. you could use a single formula like =SUMIF perhaps

[EDIT: ""Double-clicking the bottom right of E5 doesn't work"" - double clicking the corner works IF there is a list already next to it so it can copy its length. ]