So I am building a simple expense tracker for a new car. In essence, I want to put the expense for a single purchase in column A and with the running total in column B and setting up the formula for 1,000 rows but...

When I put in (for example) $1 in A1 and $2 in A2, I do not want $3 to show in B2 through B1000. Only want the total to show in B2 but have B3 - B1000 blank.

Anybody know what else with the formula (if it's correct that is) of =(B1+A2) should be in column B?

Right now, this is what is showing:

(columns are shown in reverse due to this forum's software formatting)

$34.76 $34.76
$34.76
$34.76
$34.76
$34.76

I am looking for something like this (with or without the "-")

$34.76 $34.76
$ -
$ -
$ -
$ -

What modifications do I need in the formula? (Something simple I am pretty sure I am forgetting)

TIA

You should be able to either format the column B to be blank if no data or use a formula, maybe an IF formula? I usually Google what I’m trying to do and I can find what I need. I’ve gotten better with Excel over the last 4 years, but don’t have all of the formulas committed to memory.

email me the sheet as it is.

I am having trouble trying to brain your words.

If I can see the sheet, I think I can fix it.

quote:

Originally posted by sigmonkey:
email me the sheet as it is.

I am having trouble trying to brain your words.

If I can see the sheet, I think I can fix it.

On its way

I'll post back/email in a few minutes.

Sounds like what you’re looking for in B2 is:

=SUM($A$1..$A$1000)

I think.

quote:

Originally posted by jbcummings:
Sounds like what you’re looking for in B2 is:

=SUM($A$1..$A$1000)

I think.

^^^This, but I don't think the $'s are necessary. Simply enter the formula one time in B2 and you should be golden. You might also consider using a form to make input to the table even easier.

This message has been edited. Last edited by: bigdeal,

The first page (tab) is done, but I am working on the other two formulas om the second page (tab).

I'll get them done in the morning and send it back.

I'd just put "=B1 + A2" into B2, copy that cell and special paste it (formula only) into the next 998 cells in B.

flashguy

simplified:
in B2 put:
=A2
In B3 put
=B2+A3
Then drag copy the formula from B3 down to B1000. it will update each row as B(row -1) + A(row). If you drag right/left, it will update column to C/D/E/etc & make a mess

More 'correct', add a dollar sign in front of the column - =$B2+$A3 - the $ means don't change when drag copying. If you did =$B$2 + $A3, then when you drag down, it will always add B2 + A(whatever row). Sometimes excel likes to 'assume' what you want, and it always makes an ass out of itself & you. You learn to use $ a lot.

quote:

Originally posted by flashguy:
I'd just put "=B1 + A2" into B2, copy that cell and special paste it (formula only) into the next 998 cells in B.

flashguy

If B1 is a column heading (text), you'll get an error. B3 & on would work with this method.

It also might be better to drag-copy the formula in B when you add data to column A. If you copy B2 -> B1000 w/o data in A, you will get #Value errors in each cell.
You could put 0 in A3:1000, but then you'll have the running total in each of the B cells that will update as you go.
You could write a macro to auto-copy when data is added, but truthfully, drag-copying the formula whenever data is added to A is probably easier.

I just did a test. Assuming that row 1 is where the numerical entries begin, do this:
1. in cell B2 enter: =IF(ISBLANK(A2),"",(A2+B1))
2. copy that cell
3. select all cells B3 through B1000
4. Paste special (formula) into those cells

This will tell the process to ignore computations if there is no value in the new A cell (the B cell will be blank). If the value of the A cell is to be zero, then a 0 will need to be entered there. FWIW, negative values in A will be correctly processed. Calculations that result in a B value of zero will show a "0".

It is necessary to initialize B1 with the initial starting value (should be the same as A1 in this case, but need not be--one could begin with a blank A1 (or any text) and put the starting value for the sum in B1 and the process will work; what is in A1 is ignored).

It is allowed to enter a value directly into any of the B cells without disrupting the computation formulae. Doing so will reset the accumulation process beginning with the entered value. Doing this might be confusing, however, since it would result in the corresponding A value in that row being ignored.

Here's a picture of my test:
Excel test by David Casteel, on Flickr
Note that I directly entered "20" into the B9 cell where the A9 value was 45--that 45 value was ignored, and would be confusing, but the accumulation began with the B9 of 20.

You can even skip a line, but don't enter anything into the B cell if you do, and remember than the A value in the next row will be ignored and the accumulation will begin with the B value in that row.

If you want the "$" in the columns, just Format the columns to "Number" "Currency" and "$". Specify the number of decimal places you want. I chose 2 places. (Row 21 is probably like what you'd initially indicated you'd start with, but remember that the A value in that row is ignored.)
Excel test2 by David Casteel, on Flickr


flashguy

This message has been edited. Last edited by: flashguy,

Update to above.


Note: in the "Format Cells" function for the column it is possible to select displaying negative numbers in red, or inside parentheses, or both.

LS1 GTO, you have e-mail.

flashguy

Column A is where you enter the amount, right?

Flashguy 's formula is correct: If (isblank (A2), "", A2+B1)

That's assuming your data starts at row 1 and you are not using column headers.

There are so many ways to skin a cat.

Here's what I would put in column B: =IF(A1,SUM($A$1:A1),"") and copy it down to your heart's content.

quote:

Originally posted by Keystoner:
There are so many ways to skin a cat.

Yeah, we could go wild with nested =ifs() and VBA, but I tried to keep it to addition & drag paste.
When coworkers ask me for something like this, I usually shoot them something with VBA - gotta keep them dumb so I can ride out my days

quote:

Originally posted by Keystoner:
Here's what I would put in column B: =IF(A1,SUM($A$1:A1),"") and copy it down to your heart's content.

To explain what this does for the OP: If A1 has data (A1=True), sum column A from A1 to current row(when you copy it down, it will increment the rows on the right of the :, but not left because of the $ ). If A1 does not have data (A1 = False), show blank.
The only pitfall is it will show an error if you put text in column A - I would leave it alone because it's an easy mistake to spot/fix and the formula to 'fix' it is long & will just prompt you to type a number in. Or you could restrict entry to only numbers in the data tab/data validation.
For me, anytime I get more than 2 nested IF or 3-4 IFS in a formula, it's time to switch to VBA. I go cross-eyed looking at a bunch of ,"", and having )))) at the end is always fun to figure out just how many parenthesis you should use.

snidera, you love these:
=((((((((((G25*2)+G24)*2)+G23)*2)+G22)*2)+G21)*2)+G20)+(-64*G26) -- evaluates a 7-bit column binary from the image of an IBM card rows 0 (LSB) to 6 (sign) in spreadsheet rows 20 through 26.

=(SIN(RADIANS((17/64)*(AL14*360))))*63 -- one of 256 cell values to build sine waves at various frequencies for Allen Organ Tone Cards

They're part of a complex spreadsheet I built over the years (many iterations) to decode and design Tone Cards for Allen Digital Organs. (No, I don't work for Allen -- it was a labor of love as an amateur organist.)

flashguy

quote:

Originally posted by flashguy:
...+(-64*G26)

No parenthesis or '+' sign needed here...

quote:

Originally posted by flashguy:
=(SIN(RADIANS((17/64)*(AL14*360))))*63

You could omit parenthesis here too:
=(SIN(RADIANS(17/64*AL14*360)))*63

quote:

Originally posted by Keystoner:

quote:

Originally posted by flashguy:
...+(-64*G26)

No parenthesis or '+' sign needed here...

Agreed, but I'm a "belt and suspenders" kind of guy. (I use "Oxford commas", too.)

flashguy

Interesting that we've not heard from LS1 GTO on how his project is coming. I know sigmonkey has been in touch via e-mail and I also sent e-mail to him. I'd have expected some indication of success by now.

flashguy