Excel Help Needed

[SirBruce]

Here's an example of the type of data I deal with:

Code:

     A          B          C          D          E
                 Jan       Feb       Mar       Apr
Foo            0          1000                 10000
Bar                        0         2000
Baz                        0         10000    10000

Now, I'd like to have a row that maintained a running total for all the cells in each column.  However, as you note, some cells are empty.  One alternative is to create a custom spreadsheet to fills in the cells either with previous data or custom data, by hand.  But that's a real pain to maintain.

What I'm really looking for is a formula that will, for each column, sum all the cells in the column.  If a cell is empty, it needs to use the value from the last non-empty cell.  And it needs to be dynamic enough to update appropriately if I later fill in an empty cell.

Bruce

[Dark Vengeance]

Why not just fill in zeroes in the blanks, and use a sum?

Either that, or do a custom formula for each column, and only add up the relevant cells?

Bring the noise.
Cheers.............

[SurfD]

its been ages since i did anything excell related at all, but I seem to remember that underneath the hood, that fuckers formulas could practically be used as a fully functional coding language.

Is it not possible to write someting that sums stuff, and simply throw in an if/then statement that ignores the cells with a value /= a number?

[SurfD]

Why not just fill in zeroes in the blanks, and use a sum?

Either that, or do a custom formula for each column, and only add up the relevant cells?

Bring the noise.
Cheers.............

I would guess that he cant do the first, because the sheer size of the data samples he works with would make manually filling in all the blank cells with 0s far more time consuming then practical.

I would also guess that he cant do the second, because the blank cells probably change from sample to sample, so the random nature of their placement would preclude making a meaningfull custom formula by just targeting individual non blank cells.

Pretty sure what he wants is something that can look at an entire column, and dynamicly adjust to the presense of a blank cell, regardless of where it appears in the column.

[Dark Vengeance]

I would guess that he cant do the first, because the sheer size of the data samples he works with would make manually filling in all the blank cells with 0s far more time consuming then practical.

I would also guess that he cant do the second, because the blank cells probably change from sample to sample, so the random nature of their placement would preclude making a meaningfull custom formula by just targeting individual non blank cells.

Pretty sure what he wants is something that can look at an entire column, and dynamicly adjust to the presense of a blank cell, regardless of where it appears in the column.

Tools>Filter>AutoFilter (this is a toggle so just redo to remove the filters)

Use the flter to show blanks in each column, have a 0 on the clipboard....highlight the displayed blanks and paste. Reset the filter to all, rinse repeat for each column.

Then just auto-sum on your columns.

Bring the noise.
Cheers.............

[SirBruce]

I think you're missing the point.  You can't assume the empty cell is value 0.  An empty cell needs to use the last previous value available in its row.

Bruce

[SurfD]

Doesent that break somehow if the first cell in the row is a blank/non numeric value?  Then there would be NOTHING acceptable to input into the blank cell (there being no cells previous to that in the row with a value to input).

There should be a way to create some kind of if/then statement to modify the values being summed, but I am unsure how you would go about doing it.

[SirBruce]

Doesent that break somehow if the first cell in the row is a blank/non numeric value?  Then there would be NOTHING acceptable to input into the blank cell (there being no cells previous to that in the row with a value to input).

Yes, but then you CAN assume 0.  I admit, I did not make that clear in my design document.

There should be a way to create some kind of if/then statement to modify the values being summed, but I am unsure how you would go about doing it.

I've been told by people that Excel could do it, but none of them have been able to come up with a formula for how.

Bruce

[Dark Vengeance]

Yes, but then you CAN assume 0.  I admit, I did not make that clear in my design document.

Design document? For an Excel spreadsheet?

I've been told by people that Excel could do it, but none of them have been able to come up with a formula for how.

Bruce

It may not be something you can do all with one formula....you may have to use multiple formulas to get the desired result. Personally, from your description, I can't figure out WTF you're trying to do. If every cell has a value, you can just auto-sum.....especially if blanks can be assumed as 0.

Make with some specifics, plz.

Bring the noise.
Cheers............

[SurfD]

Well, given that this is bruce, and he is fudging around with a spreadsheet, I might hazard a guess as to this having something to do with his "Great List of MMOG Subscription Numbers", but as to what he is trying to do with it, I have no idea either.

[SirBruce]

It may not be something you can do all with one formula....you may have to use multiple formulas to get the desired result. Personally, from your description, I can't figure out WTF you're trying to do. If every cell has a value, you can just auto-sum.....especially if blanks can be assumed as 0.

Make with some specifics, plz.

I thought I *was* specific enough.  See my first post.

Code:

     A          B          C          D          E
                 Jan       Feb       Mar       Apr
Foo            0          1000                 10000
Bar                        0         2000
Baz                        0         10000    10000

The Total of B shoudl be 0.  The Total of C should be 1000.  The Total of D should be 13000, NOT 12000.  The total of E should be 22000, NOT 20000.

IF A CELL IS EMPTY, USE THE VALUE IN THE LAST NON-EMPTY CELL IN THE ROW -- UNTIL YOU HIT COLUMN A, THEN ASSUME 0.

Bruce

[sidereal]

It might be possible to do that recursion inline, but it's questionable and I don't think it's worth it.

Here's a reasonable alternate way of doing it:

1. Create a second sheet that mirrors the first one.
2. Assuming the data at the first one starts at A1, enter the following in A1 of the second sheet: =IF(ISBLANK(Sheet1!A1),0,Sheet1!A1) .  Adjust 'Sheet1' to be whatever your source sheet is named.  This will seed the 0 or the first row of source data in the first row.
3. Copy that formula across the first row to however many columns you have.
4. In A2 of the second sheet, enter: =IF(ISBLANK(Sheet1!A2),A1,Sheet1!A2) .  This will either enter the value from the source sheet or the previous row of this sheet if that's blank.
5. Copy that last formula into all of the remaining cells of the second sheet (up to the height and width of the source data)

This will give you a mirror of the source data with all of the blanks filled in appropriately.  Then you can sum those.

[SirBruce]

Will Sheet2 update dynamically if a blank cell in Sheet1 is filled in?

Bruce

[Luke]

You could do it by using a conditional array formula:

{=SUM(IF(ISBLANK(D3:D5),C3:C5,D3:D5))}

This will sum the figures in the specified column if they exist, or check back in the previous cell if they don't exist in the column being summed. This works for your example, because there's no cell which has two blanks in the previous months. If you have two blanks you have to nest the IF statements:

{=SUM(IF(ISBLANK(E3:E5),IF(ISBLANK(D3:D5),C3:C5,D3:D5),E3:E5))}

You need to do as many IF(ISBLANK... statements as you expect to have empty cells for any particular entry.

At the start of a row, when there are no numerical values in prior columns for that row, it won't add anything. I'm assuming something about the makeup of your spreadsheet here, but if you do have numerical values in rows prior to the first, you should be OK by having the summing formula for that column not check back so far.

The curly braces around it indicate it's an array formula, which means you need to hit ctrl-shift-enter rather than just enter after you've finished typing it. This is how it knows to check the appropriate row for that value.

This has the possibly dubious advantage of keeping the cells that you don't enter values for blank. If you'd prefer to have values in them, use the conditional part of the formula (the IF(ISBLANK... part) separately in the blank cells to put the required value there (you can have it put the value there with an indicator, such as a different color, that the value was put in automatically rather than manually), then change the summing formula to simply SUM (or SUMIF if there are still going to be blank cells anywhere) the column.

Did all that make sense?


Luke

[SirBruce]

Makes sense, Luke.  Unfortunately it will make adding new columns too much of a pain in the ass, I think.  I guess Excel just can't do what I want.  I guess I'm better off maintaining a seperate sheet and filling in the values by hand or via sidereal's method.

Bruce

[Luke]

Not knowing what you're using it for I can't make a direct suggestion, but are you thinking you need to do the nesting manually for each one? Unless you want the automatic fill-in of values in the columns themselves, in which case you need to write it once and copy it to each of the blank cells individually, all you have to do is make a "Total" row, and type in one copy of the formula, complete with, say, 6 layers of nesting, and copy and paste it across the row underneath all the columns you want summed. It will copy the array part, and the nesting won't care whether the most recent value is last month or six months ago, it will just take the most recent figure available.

When you add a new column for a new month, just copy the "Total" cell to that column too, no changes needed. When you make a new row, make sure to do it within the summed range using Insert|Row, and it will be automatically added to the total.

Perhaps I'm misconstruing your requirement, and it's not like there aren't a million things in Excel that are too clumsy to be used in reality, but this isn't supposed to be one of them :)


Luke

[sidereal]

Will Sheet2 update dynamically if a blank cell in Sheet1 is filled in?

Yes.

The more I think about it, the more I think just filling in the blank cells with a common formula is the best thing to do.  If you don't want to pollute your source data, just make a copy of the whole sheet.

Then copy this formula:
=IF(ROW()=1,0,INDIRECT(ADDRESS(ROW()-1,COLUMN())))

Then select select the whole set of data cells.
Then go to Edit.../Go To.../Special.../Blank Cells
This will select only the blank cells in that area.
Then paste the formula.

[SirBruce]

Not knowing what you're using it for I can't make a direct suggestion,

I don't understand the question.  I can't explain what I'm using it for any more than I already have - the empty cell has to use the value from the previous one.  Yes, I'll add new rows and columns and whatever I need to.  The formula needs to handle that or it's not worth very much.  If you have suggestions that are dependant on what I'm using it for, feel free to give them along with their appropriate situation.

 but are you thinking you need to do the nesting manually for each one? Unless you want the automatic fill-in of values in the columns themselves, in which case you need to write it once and copy it to each of the blank cells individually, all you have to do is make a "Total" row, and type in one copy of the formula, complete with, say, 6 layers of nesting, and copy and paste it across the row underneath all the columns you want summed. It will copy the array part, and the nesting won't care whether the most recent value is last month or six months ago, it will just take the most recent figure available.

Sorry, I don't understand this at all.  Previously you suggested:

{=SUM(IF(ISBLANK(E3:E5),IF(ISBLANK(D3:D5),C3:C5,D3:D5),E3:E5))}

You need to do as many IF(ISBLANK... statements as you expect to have empty cells for any particular entry.

To me this suggests when I add column F I'll need to shift everything to F, E, D, C, etc. manually.  If you're saying it will do so automatically, that's cool, but my depth will have to be, like, 30 things deep.  So my formula for the first column will have to be, like, A3:A5, A5:A5, etc. 30 times.  And it won't know in the next column to increment only one of those to B, and then only one of those to C, and so on.  Sorry the terminology is confusing but I don't know Excelspeak.

When you add a new column for a new month, just copy the "Total" cell to that column too, no changes needed. When you make a new row, make sure to do it within the summed range using Insert|Row, and it will be automatically added to the total.

I don't really understand this paragraph as I don't know how one would make a new row without InsertRow, since naturally it'd have to be before the Total row.

Bruce

[Fargull]

Bruce,

How long you got for this to be solved?  I can probably do it, but do not have access to Excel from home.  Will tackle it early Monday and post back here.  Should be pretty simple actually.  One question though, if you do not fill in the cell and just leave it blank, would it be okay to replicate the data from the previous collum?

[SirBruce]

I don't need to solve it right away.  Take your time.

I would prefer not to have the sheet replicate the data and fill in the cell, no.  I've aparrently already got a formula to do that (haven't tried it yet), and in that case I may as well just do it by hand.  The goal was not to have to maintain a seperate Sheet with filled in cells, and simply have all the magic take place in the formula for the totals row.

Bruce

[Luke]

To me this suggests when I add column F I'll need to shift everything to F, E, D, C, etc. manually.  If you're saying it will do so automatically, that's cool, but my depth will have to be, like, 30 things deep.  So my formula for the first column will have to be, like, A3:A5, A5:A5, etc. 30 times.  And it won't know in the next column to increment only one of those to B, and then only one of those to C, and so on.  Sorry the terminology is confusing but I don't know Excelspeak.

The nesting only needs to be as deep as you expect to have months without values. If you expect to get values for each row once every six months, then you only need six layers of nesting. In the same way that Excel updates cell references when you copy a formula from once cell to another (i.e. if you have a formula SUM(A1:A4) in a cell, and copy it one space to the right, it will now say SUM(B1:B4)), it will update the cell references in the conditional array. As long as there is a value within the last 6 months (assuming six layers of IF(ISBLANK... statements in your copied formula), then it will pick the most recent value. Because it will automatically pick only the most recent value, you don't have to customise it at all - the IF statements will just cast back one month's figures at a time until they find an entry, as long as it's within the 6 most recent months. Put another way, the job of the nested if statements in the array is to make sure it does actually know to increment only the rows that need it, and only just as much as they need incrementing.

If you don't have values more recent than six months, then you need to increase the layers of nested IF(ISBLANK... statements. Excel has a hard limit on the length of a formula though, and you probably won't get more than a dozen layers of nesting before you hit that limit, so this solution won't work for you if you expect to have entries that don't get updated at least once a year or so. My assumption was that this was something to do with your subscription spreadsheet, in which case most figures are updated at least annually, I think.

I don't really understand this paragraph as I don't know how one would make a new row without InsertRow, since naturally it'd have to be before the Total row.

This was more to do with making sure that the row you insert is within the summed rows, rather than the way you make the new row. I sometimes forget and make a new row one row above the total row, and then wonder why that row isn't included in the total.

While this solution has the benefit of leaving your data untouched, if you're not comfortable with the formula used, it may be best not to go down that path. If you don't mind autofilling in currently blank monthly figures and then just summing the column, that may be a simpler way to go (Sidereal's suggestion is a good way to do that, though if I was reading your spreadsheet I'd want some indicator that it's an autofilled number and not an updated one. Check the Excel help under conditional formats for examples).
 

Luke

[SirBruce]

The nesting only needs to be as deep as you expect to have months without values. If you expect to get values for each row once every six months, then you only need six layers of nesting

You're missing my point about the first column.  How would I write the formula to work for the FIRST column, since I'll need six months of nested variables that "aren't there"?  And then how would it be able to update that for the next column?

I think the answer, "the first X columns you need to do the formula by hand, where X is the number of months deep" which could be a lot more than six.

I don't really understand this paragraph as I don't know how one would make a new row without InsertRow, since naturally it'd have to be before the Total row.

This was more to do with making sure that the row you insert is within the summed rows, rather than the way you make the new row. I sometimes forget and make a new row one row above the total row, and then wonder why that row isn't included in the total.

Er... then your comment about Insert Row didn't mean anything?  In which case, how would I add a new row at all, without having to edit the totals line?  Unless you mean the totals sum has a range of, say, hundreds of cells down past where I'm likely to go anyway.

While this solution has the benefit of leaving your data untouched, if you're not comfortable with the formula used, it may be best not to go down that path.

The only thing I'm uncomfortable with the formula is the objection I've already given.

Bruce

[Luke]

Here's the formula for the November 04 month of the current version of your spreadsheet:

{=SUM(IF(ISBLANK(CR2:CR30),IF(ISBLANK(CQ2:CQ30),IF(ISBLANK(CP2:CP30),IF(ISBLANK(CO2:CO30),IF(ISBLANK(CN2:CN30),IF(ISBLANK(CM2:CM30),CL2:CL30,CM2:CM30),CN2:CN30),CO2:CO30),CP2:CP30),CQ2:CQ30),CR2:CR30))}

Just copy and paste it to each cell in row 31 on Sheet 1.

It will automatically update itself for each column. For the first six columns it will give you a #REF error as a result, because some columns don't exist, and indicate in the formula where these errors are the same way. Within the formula, delete any IF statement with the #REF error in it, and this will resolve. Don't forget the ctrl-shift-enter when you do this. There's only one title during this time, so you don't actually need any special formula to sum it anyway.

Excel limits nesting to seven layers, it appears, and there are something less than a dozen instances of there being more than a seven month gap between data points subsequent to a title's launch. You'll either need to put in a data point for those instances, or go the route of maintaining an autofilled data set on a separate worksheet. This autofilled data set, on Sheet 2 for example, can simply serve as the data set for the totals on Sheet 1 if that makes it any neater.  

New rows can be added by inserting them at or above row 30, and the total formula will automatically stretch to accommodate them (there's nothing special about this, it's how Excel treats any sum, and I only mention it because I seem to have confused the issue with my original comment about inserting rows).


Luke

[SirBruce]

Formula won't work for me, then.  Ahh well.  It was a good try, folks.  Looks like Excel just can't do it.

Bruce

[Pineapple]

Formula won't work for me, then.  Ahh well.  It was a good try, folks.  Looks like Excel just can't do it.

Bruce

Excel can do anything you want with numbers. It's just a matter of getting it set up to do that.

I used to know a guy that would be able to do this, but I dont know him now. I certainly dont know how.

Even if it takes some scripting in the VB area, it can be done somehow. I've seen Excel do some amazing crap before, I dont see why it cant do this too.

Might check on some Excel guru forums if you havent already.

[SirBruce]

I have checked.  You'd think it was a common problem with empty cells.  But no, most people just fill those cells in, which is what I shall do.

Bruce

[slog]

This is the type of thing I would do a macro for in Visual Basic.  

The Macro would copy the entire sheet, replace the blanks with the value of the cell with the value of the cell (position -1) on the new sheet, Find the Sum, paste the value the sum total onto the original Sheet, and then clear out the new sheet.

Then I would just attach a button linked to the macro.

[Righ]

Something like this would fill in the blanks with the value from the previous column:

Code:

Range("B1:M30").Select
Set myRange = Selection
For Each Cell In myRange
    If IsEmpty(Cell.Value) Then
        Cell.Select
        Cell.Value = ActiveCell.Offset(0,-1).Value
    End If
Next

[SirBruce]

How would I use such code in Excel?

Bruce

[Righ]

Tools/Macro/Visual Basic Editor

Paste it into the procedure window, adjust the bounds in the initial range, and run it (run menu in VBE).

[slog]

Macros are not too hard, once you get used to them.  Generally speaking, when you want to do two things with one cell, you use a Macro to tell Excel which operation you want run first.

If you have never done one before, you will probably need assistance.

[SirBruce]

If I get lost, I can always ask the fucking paperclip.

Bruce

[Fargull]

If you just want to self replicate the previous data across the board you can just use =IF(,*Current Cell*,*Previous Cell*) in the cell.

Place in the second Collumn.  So

  1  2  3  4  5
1    X  X X  X
2    X  X X  X

And such.. as soon as you fill in the first cell the rest will populate.  You can modify this as needed to check a qualifier.  Or you can setup Righ's Macro, which will work.  If you want to check a Qualifier first to see if data should be entered, then you want to use this...

=IF(ISTEXT(Collumn Header or Other Qualifier),IF(,*Curent Cell*,*Previous Cell*),)

Which will check to see if the Collumn header has been entered, thus if your using dates.. say Jan Feb Mar Apr it would check to make sure Feb had been entered before populating the cell with the previous Cell.  If Feb does not exist, it will leave that number at 0.

[Viin]

Hmm it sounds to me like you could do this:

1) Create spreadsheet with your existing columns and rows
2) select all blank fields that will be populated later, except the first column
3) Paste this formula:

Code:

=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

4) Start filling in the values you know, replacing that formula in those cells.

Cavate: When you create a new column or row you will need to populate the empty cells with this formula.

Seems easy enough. If you use 'select blank cells' method in the previous post you could do this to already existing data. It even takes blank cells and uses 0 instead.

[SirBruce]

Okay, so what I finally ended up doing was just creating a second worksheet for using "derived" values, so I don't care if the cells are filled in on that page.  Then I could use it to generate totals and I could do all sorts of niftier stuff with that data.  Here's the final formula I wound up using:

=IF(ISBLANK(Sheet1!C2),Sheet2!B2,Sheet1!C2)

I filled in the first column (A) with all zeroes, and then I copied the formula above across all the remaining cells so Excel would use relative values as appropriate.  Works great!

Thanks to everyone who offered their thoughts and ideas in this thread.  You would think this would be a common need in Excel, but there's no LastRowValue or LastColumnValue function.

Bruce