Excel Help - Page 7 - Antsmarching.org Forums

Go Back   Antsmarching.org Forums > General Discussion > alternativefactsC


Want to hide all ads on Ants? Click here
Reply
 
Thread Tools Display Modes
Old 05-16-2017, 12:20 PM   #181
Arby
We Have The Meats
 
Arby's Avatar
 
Join Date: Jul 2004
Posts: 84,550

Shows Seen: 24

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, 01:39 PM   #182
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    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.
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 02:46 PM   #183
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 11,000

    Shows Seen: 12
    Send a message via ICQ to swordo84 Send a message via MSN to swordo84

    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?
    __________________
    Eat Drink & Be Merry

    Quote:
    Originally Posted by ARFiredancer
    ...I don't know what I am trying to say...I shouldn't post when I am high........
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 02:51 PM   #184
    thebridge15
     
    thebridge15's Avatar
     
    Join Date: Jun 2008
    Posts: 66,348

    Shows Seen: 18

    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 online now   Reply With Quote
    Old 05-19-2017, 02:53 PM   #185
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    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.
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 02:57 PM   #186
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    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
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 02:59 PM   #187
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 11,000

    Shows Seen: 12
    Send a message via ICQ to swordo84 Send a message via MSN to swordo84

    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....
    __________________
    Eat Drink & Be Merry

    Quote:
    Originally Posted by ARFiredancer
    ...I don't know what I am trying to say...I shouldn't post when I am high........
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 03:01 PM   #188
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    Re: Excel Help

    What's the highest number in that column?
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.
    rconverse is offline   Reply With Quote
    Old 05-19-2017, 03:02 PM   #189
    thebridge15
     
    thebridge15's Avatar
     
    Join Date: Jun 2008
    Posts: 66,348

    Shows Seen: 18

    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 online now   Reply With Quote
    Old 05-19-2017, 03:03 PM   #190
    swordo84
    . .
     
    swordo84's Avatar
     
    Join Date: May 2002
    Location: Canada
    Posts: 11,000

    Shows Seen: 12
    Send a message via ICQ to swordo84 Send a message via MSN to swordo84

    Re: Excel Help

    Quote:
    Originally Posted by rconverse View Post
    what's the highest number in that column?
    4456 .
    __________________
    Eat Drink & Be Merry

    Quote:
    Originally Posted by ARFiredancer
    ...I don't know what I am trying to say...I shouldn't post when I am high........
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 03:12 PM   #191
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    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.
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.

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

    Shows Seen: 12
    Send a message via ICQ to swordo84 Send a message via MSN to swordo84

    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!
    __________________
    Eat Drink & Be Merry

    Quote:
    Originally Posted by ARFiredancer
    ...I don't know what I am trying to say...I shouldn't post when I am high........
    swordo84 is offline   Reply With Quote
    Old 05-19-2017, 03:19 PM   #193
    rconverse
     
    rconverse's Avatar
     
    Join Date: May 2003
    Location: It's how, not where. I live to dance!
    Posts: 63,209

    Shows Seen: 25

    Re: Excel Help

    Enjoy the weekend!!
    __________________
    Quote:
    Originally Posted by opshannon View Post
    You're right about the shift to the right that the board took after it reopened. And it's not even thoughtful righties, it's talk radio righties. Got no patience for them whatsoever. We'll see. Maybe I'll see what I can tolerate.
    rconverse is offline   Reply With Quote
    Reply

    Bookmarks

    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 -4. The time now is 01:29 PM.


    Powered by vBulletin® Version 3.8.6
    Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.


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