Roger’s Excel Thread - Page 7 - Antsmarching.org Forums - Dave Matthews Band Discussion
Old 05-16-2017, 09:20 AM   #181
Arby
We Have The Meats
 
Arby's Avatar
 
Join Date: Jul 2004
Posts: 89,484

Shows Seen: 24

DMB Hub Stubs: 12

My Tour Central Stats

Re: Excel Help

Quote:
Originally Posted by BTBaboon View Post
Are all the formulas in the same column, referencing two adjacent columns?
No, we've got a 32x40 grid with formulas on each of the 14 tabs


But good news: we are moving forward with the change to add =iferror to the formula. It'll take a min, but itll be a huge time saver long term.

Thanks again for your help, guys
__________________
-Arby
Arby is offline   Reply With Quote

  • Want to hide all ads on Ants? Click here
  • Old 05-16-2017, 10:39 AM   #182
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Arby View Post
    No, we've got a 32x40 grid with formulas on each of the 14 tabs


    But good news: we are moving forward with the change to add =iferror to the formula. It'll take a min, but itll be a huge time saver long term.

    Thanks again for your help, guys
    Sounds like a strong setup.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 11:46 AM   #183
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 15,963

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Excel people - I call on you for help with what I suspect is an easy forumla I just can't wrap my head around.

    I have 18,000+ rows of simple number (no decimal points). I need to gather data on how many of these are <= 30, 31-60, 61-90 etc. etc. etc.

    Help, please?
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 11:51 AM   #184
    thebridge15
     
    thebridge15's Avatar
     
    Join Date: Jun 2008
    Posts: 75,098

    Shows Seen: 20

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    you can use countif and then set the criteria
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    bills are paying the jags o-line coach 4 million this year.

    that will be a tough one for the aliens to figure out.
    thebridge15 is offline   Reply With Quote
    Old 05-19-2017, 11:53 AM   #185
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by swordo84 View Post
    Excel people - I call on you for help with what I suspect is an easy forumla I just can't wrap my head around.

    I have 18,000+ rows of simple number (no decimal points). I need to gather data on how many of these are <= 30, 31-60, 61-90 etc. etc. etc.

    Help, please?
    Are you very good with pivot tables? You could create a pivot table, then set the rows to grouping by those intervals. That's the easiest way.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 11:57 AM   #186
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Like I just threw one together real quick...

    Row Labels Count of Number
    1-30 30
    31-60 30
    61-90 30
    91-120 30
    121-150 30
    151-180 30
    181-210 30
    211-240 30
    241-270 30
    271-300 30
    301-330 30
    331-360 30
    361-390 30
    391-420 30
    421-450 30
    451-480 30
    481-510 30
    511-540 30
    541-570 30
    571-600 30
    601-630 30
    631-660 30
    661-690 30
    691-720 30
    721-750 30
    751-780 30
    781-810 30
    811-840 30
    841-870 30
    871-900 13
    Grand Total 883
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 11:59 AM   #187
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 15,963

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by thebridge15 View Post
    you can use countif and then set the criteria

    I tired and couldnt get the formulation just right. Can you 'example' me ?

    Quote:
    Originally Posted by rconverse View Post
    Are you very good with pivot tables? You could create a pivot table, then set the rows to grouping by those intervals. That's the easiest way.
    No....
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 12:01 PM   #188
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    What's the highest number in that column?
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 12:02 PM   #189
    thebridge15
     
    thebridge15's Avatar
     
    Join Date: Jun 2008
    Posts: 75,098

    Shows Seen: 20

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    wait shit, roger can correct me but I don't think you can use a dual conditional (like 4<X<10) in a countif
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    bills are paying the jags o-line coach 4 million this year.

    that will be a tough one for the aliens to figure out.
    thebridge15 is offline   Reply With Quote
    Old 05-19-2017, 12:03 PM   #190
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 15,963

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    what's the highest number in that column?
    4456 .
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 12:12 PM   #191
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by thebridge15 View Post
    wait shit, roger can correct me but I don't think you can use a dual conditional (like 4<X<10) in a countif
    You can but he'd have way too many iterations to get up to 4,456. It takes 149 intervals to get to 4,456 in groups of 30.

    Quote:
    Originally Posted by swordo84 View Post
    4456 .
    You're going to need to use a pivot table. It's really not difficult.

    I'm going to assume your data is set up in a traditional spreadsheet format.

    Select cell A1 in that worksheet.
    Go to Insert and then click on pivot table (It's just under and to the left of the Insert tab of the ribbon)
    A new little screen will appear - just click on okay
    That will create a new worksheet (don't worry, nothing happens to the old worksheet. it's still fine)
    You will see a menu on the right that says Pivot Table Fields
    Select the column with your number and drag it down to the box where it says "rows"
    Select the column again and drag it to where it says "values"

    You will see the pivot table start to the build on your left.

    There should be two columns in your pivot table...

    Row Labels & Sum of (Whatever is the name of your column)

    Right click on the Row Labels column and select "Group" and enter
    Starting at 1
    Ending at 5000
    By 30

    Right Click on "Sum of (Whatever is the name of your column)"
    Summarize values by - Count

    You're done.

    Last edited by rconverse; 05-19-2017 at 12:14 PM.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 12:18 PM   #192
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 15,963

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    You can but he'd have way too many iterations to get up to 4,456. It takes 149 intervals to get to 4,456 in groups of 30.



    You're going to need to use a pivot table. It's really not difficult.

    I'm going to assume your data is set up in a traditional spreadsheet format.

    Select cell A1 in that worksheet.
    Go to Insert and then click on pivot table (It's just under and to the left of the Insert tab of the ribbon)
    A new little screen will appear - just click on okay
    That will create a new worksheet (don't worry, nothing happens to the old worksheet. it's still fine)
    You will see a menu on the right that says Pivot Table Fields
    Select the column with your number and drag it down to the box where it says "rows"
    Select the column again and drag it to where it says "values"

    You will see the pivot table start to the build on your left.

    There should be two columns in your pivot table...

    Row Labels & Sum of (Whatever is the name of your column)

    Right click on the Row Labels column and select "Group" and enter
    Starting at 1
    Ending at 5000
    By 30

    Right Click on "Sum of (Whatever is the name of your column)"
    Summarize values by - Count

    You're done.

    Ok, great - thanks for the help. I'm about the leave for the weekend so this is now a Tuesday project (yay for Canadian long weekends!)

    If I still have troubles on Tuesday I'll reach out - thanks again!
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 12:19 PM   #193
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Enjoy the weekend!!
    rconverse is offline   Reply With Quote
    Old 05-23-2017, 07:32 AM   #194
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 15,963

    Shows Seen: 12

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    Enjoy the weekend!!
    Worked like a charm, big big thanks again!
    swordo84 is offline   Reply With Quote
    Old 05-26-2017, 06:07 AM   #195
    mr. testaverde
    King of Prussia
     
    Join Date: Aug 2003
    Location: location location
    Posts: 15,576

    Shows Seen: 58

    DMB Hub Stubs: 25

    My Tour Central Stats

    Re: Excel Help

    today i learned that you can unlock a password protected sheet without knowing the password, just use a bit of code to do it
    __________________
    Mmm boy that Hennigans goes down smooth. And afterwards you don't even smell.
    mr. testaverde is offline   Reply With Quote
    Old 06-15-2017, 12:00 PM   #196
    Arby
    We Have The Meats
     
    Arby's Avatar
     
    Join Date: Jul 2004
    Posts: 89,484

    Shows Seen: 24

    DMB Hub Stubs: 12

    My Tour Central Stats

    Re: Excel Help

    Did a google search, but Im not really finding specifically what Im looking for.

    Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

    But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

    Then this same scenario actually needs to happen one more time with rows 947-952.

    Any help is appreciated!
    __________________
    -Arby
    Arby is offline   Reply With Quote
    Old 06-15-2017, 12:03 PM   #197
    daveshookme
    Altuve wore a wire
     
    daveshookme's Avatar
     
    Join Date: Jun 2009
    Location: Supreme Courtyard by Marriott
    Posts: 62,665

    Shows Seen: 54

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Arby View Post
    Did a google search, but Im not really finding specifically what Im looking for.

    Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

    But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

    Then this same scenario actually needs to happen one more time with rows 947-952.

    Any help is appreciated!


    I know what you're talking about, and I have no idea. I can have rows 1-6 frozen (pretty sure that feature is still available, haven't used it for a few of the later versions of excel but there's zero reason for them to take it out), but I don't know how to replace that with the new rows when you scroll down.
    __________________
    Tape Cover Artwork
    daveshookme is offline   Reply With Quote
    Old 06-15-2017, 12:14 PM   #198
    sheldonlevene
    Mr. 1k
     
    sheldonlevene's Avatar
     
    Join Date: May 2009
    Posts: 19,695

    Shows Seen: 35

    DMB Hub Stubs: 15

    My Tour Central Stats

    Re: Excel Help

    The closest thing I can think of is using the split functionality...otherwise I think your SOL Arby.
    __________________
    Quote:
    Originally Posted by simplelife08 View Post
    our sexual interactions are incredibly clean-almost in a clinical sense. No emotion, minimal ejaculate, always just me
    sheldonlevene is offline   Reply With Quote
    Old 06-15-2017, 12:35 PM   #199
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Arby View Post
    Did a google search, but Im not really finding specifically what Im looking for.

    Anyone here know how to do Freeze Panes on multiple sections? So...what Im looking for is rows 1-6 to be frozen. Easy enough.

    But then when I scroll down to row 474, I want 474-479 to take the place of the first freeze. They are similar headlines, so rows 1-6 can fall out of view and 474-479 will replace as the frozen section.

    Then this same scenario actually needs to happen one more time with rows 947-952.

    Any help is appreciated!
    Why do you have multiple column headings in the same columns? Makes no sense.

    Okay, that said, the only way to do this is with a macro. Is that okay?
    rconverse is offline   Reply With Quote
    Old 06-15-2017, 12:42 PM   #200
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Related question, but without multiple column headings in the same column:

    Anyone know how to get the column headings to replace the, A, B, C etc...labels when you scroll down? I saw that in a spreadsheet recently and thought it was cool.
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 06-15-2017, 12:46 PM   #201
    sheldonlevene
    Mr. 1k
     
    sheldonlevene's Avatar
     
    Join Date: May 2009
    Posts: 19,695

    Shows Seen: 35

    DMB Hub Stubs: 15

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Related question, but without multiple column headings in the same column:

    Anyone know how to get the column headings to replace the, A, B, C etc...labels when you scroll down? I saw that in a spreadsheet recently and thought it was cool.
    Toggle headings on the "VIEW" tab? Not sure if that's what you're asking.
    __________________
    Quote:
    Originally Posted by simplelife08 View Post
    our sexual interactions are incredibly clean-almost in a clinical sense. No emotion, minimal ejaculate, always just me
    sheldonlevene is offline   Reply With Quote
    Old 06-15-2017, 12:51 PM   #202
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    No...the spreadsheet I saw had column headings in row 1. When you scrolled down, the column headings replaced the A, B, C, etc...labels. Does that make sense?
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 06-15-2017, 12:53 PM   #203
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Related question, but without multiple column headings in the same column:

    Anyone know how to get the column headings to replace the, A, B, C etc...labels when you scroll down? I saw that in a spreadsheet recently and thought it was cool.
    It's in Excel Options....

    Otions - Advanced - Display options for this worksheet

    Uncheck the first box that says show row and column labels.
    rconverse is offline   Reply With Quote
    Old 06-15-2017, 12:57 PM   #204
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    Nah, I must not be 'splaining it very well.
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 06-15-2017, 01:00 PM   #205
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Nah, I must not be 'splaining it very well.
    You are and that's exactly how to do it.

    If you do that, you will no longer see A, B, C across the top of your spreadsheet. You will only see the contents that's in Row1 of that spreadsheet.

    That said, this will remove the 1, 2,3 etc.. on the rows. It removes them both from view.

    Last edited by rconverse; 06-15-2017 at 01:01 PM.
    rconverse is offline   Reply With Quote
    Old 06-15-2017, 01:01 PM   #206
    mandy18
     
    Join Date: Feb 2002
    Location: Pittsburgh, PA
    Posts: 8,970

    Shows Seen: 55

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by Beefsteak1138 View Post
    Nah, I must not be 'splaining it very well.
    The spreadsheet may have been using a table.
    Highlight your range (or the entire worksheet); format as a table.
    After you start scrolling down, you can't see the A,B,C,D column labels anymore.
    mandy18 is offline   Reply With Quote
    Old 06-15-2017, 01:02 PM   #207
    Beefsteak1138
     
    Beefsteak1138's Avatar
     
    Join Date: May 2003
    Posts: 82,549

    Shows Seen: 10

    DMB Hub Stubs: 7

    My Tour Central Stats

    Re: Excel Help

    No it's not. I may not be an excel guru, but can follow simple instructions.
    __________________
    Quote:
    Originally Posted by Rebecca De Mornay View Post
    i wish i lived in a time where it was acceptable to have sex with kids.
    Beefsteak1138 is offline   Reply With Quote
    Old 06-15-2017, 01:03 PM   #208
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Sorry, the last step is to uncheck "show row and column headers" not labels.
    rconverse is offline   Reply With Quote
    Old 06-15-2017, 01:03 PM   #209
    mandy18
     
    Join Date: Feb 2002
    Location: Pittsburgh, PA
    Posts: 8,970

    Shows Seen: 55

    DMB Hub Stubs: 20

    My Tour Central Stats

    Re: Excel Help

    Roger's instructions will completely remove the column and row labels.
    My instructions will remove the column labels the second you start scrolling down (but the row numerical labels will remain).

    I'm not sure what else you mean.
    mandy18 is offline   Reply With Quote
    Old 06-15-2017, 01:06 PM   #210
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: Pretty Boy Blue State
    Posts: 77,945

    Shows Seen: 25

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mandy18 View Post
    The spreadsheet may have been using a table.
    Highlight your range (or the entire worksheet); format as a table.
    After you start scrolling down, you can't see the A,B,C,D column labels anymore.
    Yeah, this is the only other option I can think of right now.
    rconverse is offline   Reply With Quote
    Reply

    Tags
    excel

    Thread Tools
    Display Modes

    Posting Rules
    You may not post new threads
    You may not post replies
    You may not post attachments
    You may not edit your posts

    BB code is On
    Smilies are On
    [IMG] code is Off
    HTML code is Off

    Forum Jump


    Want to hide all ads on Ants? Click here

    All times are GMT -7. The time now is 04:30 PM.


    Powered by vBulletin® Version 3.8.14
    Copyright ©2000 - 2024, vBulletin Solutions Inc.


       
    Site LinksAbout AntsAnts MobileTweet Tweet
    Home
    Ants+
    Tour Central
    Search bar
    RSS Feeds
    About Us
    Contact Us
    The Ants Blog
    Advertise on Ants
    Privacy Policy
    Ants on your cell phone
    iAnts
    mobile news
    mobile setlists
    antslive!
    Ants' Twitter
    DMBLive Twitter
    Ants Facebook
    Ants Instagram