+ Reply to Thread
Results 1 to 30 of 30

Adding random ranges of cells in single column automatically

  1. #1
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Adding random ranges of cells in single column automatically

    Hello

    In the attached link there is an excel file with a list of numbers in column A. Basically, I would like to know how I can set it up so that I have a formula in every cell in column B that would just ignore column A unless there was a blank cell. If there is a blank space in column A, I would like the sum of the numbers up to the next blank. The number of cells between blanks will always be random. So column B is sort of like category totals. I am fairly new to excel and pretty much everything I know is self-taught, so my terminology may be off, so if you need clarification, please ask.




    https://onedrive.live.com/redir?resi...nt=file%2cxlsx
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Do you need the blank cells in column B to actually BE blank, or just LOOK blank? If it's the latter, your solution is pretty straightforward. Put the following formula in cell B2, then fill down and up through column B.

    =IFERROR(IF(ISBLANK($A2),$B1,IF(ISBLANK($A1),$A2,$B1+$A2)),$A2)

    That formula will return the desired values all through column B. You can then mask the ones between the blanks by using this conditional formatting rule for column B:

    =NOT(ISBLANK($A1))

    And setting the format for the rule to white text. This is a bit of a cheat, but if it works, it works, and it shouldn't be too hard to understand/edit in the future. If you need the 'in between' columns to actually be blank... that's a trickier nut to crack. Conceptually, it seems possible, but it'll definitely require a bit more time and a busier solution.

  3. #3
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Looking blank would be fine. How do I apply that conditional formatting?

  4. #4
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Select column B, click on conditional formatting > New Rule > Use a formula to determine... > Enter the formula I posted > click Format > select font color as white > Okay your way out

    For my own curiosity, I figured out a formula that will work without the white-text cheat. It's a bit slower and significantly more complex, though, so you might want to stick with the method above. If you're interested, here it is - paste it in B9 and fill up and down through column B:

    =IFERROR(IF(ISBLANK($A9),SUM(INDIRECT("A"&LOOKUP(2,1/(ISBLANK($A$1:$A8)),ROW($A:$A)+1)&":A"&ROW()-1)),""),SUM($A$1:$A9))

  5. #5
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Nice. I got it working with the first method and it works exactly like I had hoped, except for when my column of numbers stops, the formula keeps going. Could I make everything shut off or go blank if there were two blanks in succession?

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Adding random ranges of cells in single column automatically

    Try this in B2:
    =IF(AND(A2="",A1<>""),SUM($A$1:A1)-SUM($B$1:B1),"")
    Copy down

  7. #7
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Quote Originally Posted by Phuocam View Post
    Try this in B2:
    =IF(AND(A2="",A1<>""),SUM($A$1:A1)-SUM($B$1:B1),"")
    Copy down
    Is this supposed to replace all the other stuff or some in conjunction with it?

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Adding random ranges of cells in single column automatically

    Quote Originally Posted by 17asleep View Post
    Is this supposed to replace all the other stuff or some in conjunction with it?
    You try and test results.

  9. #9
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Quote Originally Posted by Phuocam View Post
    You try and test results.
    I haven't analyzed how it works, but it does exactly what I want it to do. thank you very much.

    I don't know if I should start a separate thread for this or not, but is there a way to find the last filled in cell in a column? For example, if in the same spreadsheet I wanted a grand total at the bottom in column C one line below the last entry in column B.

  10. #10
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    If you've used Phuocam's formula in post #6 or mine in #4, you should be able get a grand total just be summing column B. If you know what cell you want the total in, just use:

    =SUM($B:$B)

    If you don't know the specific cell, then putting the following formula in C2 and filling down should do the trick

    =IFERROR(IF(AND($A2+$B2=0,$B1<>0),SUM($B:$B),""),"")

  11. #11
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Thanks. Seems to work perfectly

  12. #12
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    OK, so if I use it in the attached table, it works fine but if I try to use it in a different application where I hav different columns an rows, I cannot m it work. I tried to change all the addresses to reflect t changes, but it won't seem to work. Could someone walk me through how the formula works so I can transfer it to t sheet that I need it in?

  13. #13
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Be sure that you're NOT entering the formula in the very first row in which you need it. Enter it in 'B2' or somewhere else down the line. As to walking you through the formula, Phuocam's rather clever solution is easier to dissect than mine, so let's break down that formula:

    =IF(AND(A2="",A1<>""),SUM($A$1:A1)-SUM($B$1:B1),"")

    In B2, if A2 is blank AND A1 is not (indicating a break in your string of numbers in A), then the formula will add together all of the values in A up to that point and subtract all of the values in B to that point, returning the solution in column B. If A2 is not blank, then the corresponding B2 entry will be blank. Essentially, each of your subtotal entries in B will be the sum of ALL previous values in A minus the previous subtotals in B, leaving the difference, which is the sum of the values in A since the last break. If you're going to modify your range, enter the formula in the column in which you want the subtotals, but NOT in the first cell in that range. Pick a cell in the middle, we'll call it row X. Change all of the "A" entries in the formula to whatever column has your values. Change row 2 in the formula to correspond to row X. Change row 1 in the formula to correspond to X-1.

    I hope that makes sense? If it doesn't, post what the new range is and we can deal in specifics.

  14. #14
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16
    Quote Originally Posted by cantosh View Post
    Be sure that you're NOT entering the formula in the very first row in which you need it. Enter it in 'B2' or somewhere else down the line. As to walking you through the formula, Phuocam's rather clever solution is easier to dissect than mine, so let's break down that formula:

    =IF(AND(A2="",A1<>""),SUM($A$1:A1)-SUM($B$1:B1),"")



    In B2, if A2 is blank AND A1 is not (indicating a break in your string of numbers in A), then the formula will add together all of the values in A up to that point and subtract all of the values in B to that point, returning the solution in column B. If A2 is not blank, then the corresponding B2 entry will be blank. Essentially, each of your subtotal entries in B will be the sum of ALL previous values in A minus the previous subtotals in B, leaving the difference, which is the sum of the values in A since the last break. If you're going to modify your range, enter the formula in the column in which you want the subtotals, but NOT in the first cell in that range. Pick a cell in the middle, we'll call it row X. Change all of the "A" entries in the formula to whatever column has your values. Change row 2 in the formula to correspond to row X. Change row 1 in the formula to correspond to X-1.

    I hope that makes sense? If it doesn't, post what the new range is and we can deal in specifics.

    This part I've got working fine. It's the part where I want a grand total at the bottom where I'm having issues

  15. #15
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Quote Originally Posted by 17asleep View Post
    This part I've got working fine. It's the part where I want a grand total at the bottom where I'm having issues
    Ah, sorry. The principle is generally the same with the 'grand total' formula in Column C:

    =IFERROR(IF(AND($A2+$B2=0,$B1<>0),SUM($B:$B),""),"")

    The IF clause identifies the spot where:
    1) A + B = 0, i.e. there are no values in A (the data column) or B (the subtotal column), so we've reached the end of the line
    AND
    2) There IS a subtotal in the spot one row above the place where we want the grand total (this assures us just one grand total, instead of a repeated total in every row after your data ends

    Where these two conditions are met, the formula returns the sum of all subtotal (column B) values in that cell. Otherwise... blank.

    Adjust the columns to suit your needs and it should work. If not, can you identify the sort of error you're getting?

  16. #16
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Here is a the exact way I am trying to use this and I cannot make it work. I think I edited the formula properly and I think I understand how it works, but I can't figure out why it doesn't. Total is supposed to be in H column

    https://onedrive.live.com/redir?resi...nt=file%2cxlsx
    Last edited by 17asleep; 03-16-2016 at 02:44 PM.

  17. #17
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    I'm not able to see the formulas you've entered, so I can't identify the source of your difficulty, but based on how it looks, enter the formula below in H3 and fill down through H. The total should only appear where it's supposed to (H21).

    =IFERROR(IF(AND($E3+$G3=0,$G2<>0),SUM($G:$G),""),"")

  18. #18
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Quote Originally Posted by cantosh View Post
    I'm not able to see the formulas you've entered, so I can't identify the source of your difficulty, but based on how it looks, enter the formula below in H3 and fill down through H. The total should only appear where it's supposed to (H21).

    =IFERROR(IF(AND($E3+$G3=0,$G2<>0),SUM($G:$G),""),"")
    That is exactly what I did and it doesn't work. Reading the formula, it makes sense to me and I think it should work, but I can't figure out why it doesn't

  19. #19
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Maybe it's an excel online issue? I've attached what I'm seeing. Are you able to try it in an offline workbook?
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    Yes, I have been using excel online and offline. Both ways are the same. I copied the formula (identical to mine) from your workbook into mine and now I get a SUM, but it is four lines below the last filled cell in the other column. I can live with that, but I would like to know WHYYYYYYYYYYY!!!!!????

  21. #21
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Can you post the workbook with it four lines below as an attachment so I can download it?

  22. #22
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    I have been trying to attach, but I cant figure out how.

  23. #23
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Go Advanced > Paperclip Icon (or Manage Attachments) > Add files > Browse > [pick file] > upload > Done

  24. #24
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    is it attached?
    Attached Files Attached Files

  25. #25
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Successfully attached. From what I can see, you've got the right formula, but it wasn't entered in the correct cell. In your attachment, H3 contains this:

    =IFERROR(IF(AND($E21+$G21=0,$G20<>0),SUM($G:$G),""),"")

    ...which has the right look, but that's the formula that should be in H21. The Cell address in the addition clause ($E21 + $G21) should always match the row the formula is in (Row 21), while the other clause ($G20<>0) should always be one less than the row the formula is in (still Row 21). Paste the formula below specifically in H3, then just fill down with it:

    =IFERROR(IF(AND($E3+$G3=0,$G2<>0),SUM($G:$G),""),"")

  26. #26
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    That odd. Dunno how that would have happened. It works now. Hmmm...what a let-down. Now everything works and I am out of work. gotta find something new to do. Thank you very much for all your help.

  27. #27
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Haha, just look busy and don't tell anyone. You'll go far.

  28. #28
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    So, I'm back. Apparently it doesn't work. The formula in column G sees the formula in column E as content and doesn't return a result until I copy the formula far enough down so that there is no formula in column E. I didn't catch that for a while because I was out of the office for a week, but now I'm banging my head on the desk again.

  29. #29
    Forum Expert
    Join Date
    05-20-2015
    Location
    Chicago, Illinois
    MS-Off Ver
    2016
    Posts
    2,103

    Re: Adding random ranges of cells in single column automatically

    Hmm. If E is your data column and G is your subtotal column, try this one in cell H2 and fill down:

    =(IF(AND(NOT(ISNUMBER($G2)),NOT(ISNUMBER($E2)),ISNUMBER($G1)),SUM($G:$G),""))

    It should count formulas returning blanks as blanks, rather than content, so it shouldn't matter what's in E or G as long as it looks blank (i.e. is not a number).

  30. #30
    Registered User
    Join Date
    03-09-2016
    Location
    Canada
    MS-Off Ver
    2013
    Posts
    16

    Re: Adding random ranges of cells in single column automatically

    This works. Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transform random entries in multiple rows to single column
    By Venkatesh.S in forum Excel General
    Replies: 5
    Last Post: 01-28-2015, 09:37 AM
  2. [SOLVED] I need to set ranges for Unique values, then from those ranges pick random cells...
    By Darth269 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-21-2014, 04:45 PM
  3. Function for adding certain random cells
    By Elite88 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-04-2013, 05:56 PM
  4. Replies: 2
    Last Post: 07-04-2012, 06:17 AM
  5. Average of several ranges in a single column
    By mjt3727 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-05-2012, 01:03 PM
  6. Random Sorting of Ranges of Cells - Excel 2002
    By COgreywolf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-28-2012, 02:12 AM
  7. Excel automatically adding single quotes to a named range
    By guindi in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2007, 03:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1