Roger’s Excel Thread - Antsmarching.org Forums - Dave Matthews Band Discussion
Old 02-24-2006, 08:31 AM   #1
TwoStep2888
 
TwoStep2888's Avatar
 
Join Date: Mar 2004
Location: WI
Posts: 71,084

Shows Seen: 7

DMB Hub Stubs: 8

My Tour Central Stats

Roger’s Excel Thread

For a Psychology project I'm doing, I need to average a series of times people go to bed/wake up, and calculate the time between. For one of the groups I polled, several people answered 12:30 AM for their bedtime, but when I average that with the other times, those 12:30s bring the average time down by about two hours, because the 12:30s are AM, and the rest are PM. Is there any way to remedy this problem? Please help me.
__________________
trouble get behind me now, trouble let me be
TwoStep2888 is offline   Reply With Quote

  • Want to hide all ads on Ants? Click here
  • Old 02-24-2006, 08:45 AM   #2
    GSUdawg521
    hbtd
     
    GSUdawg521's Avatar
     
    Join Date: Nov 2004
    Location: Southeast of disorder
    Posts: 25,114

    Shows Seen: 28

    DMB Hub Stubs: 11

    My Tour Central Stats

    Re: Excel Help

    I don't know about using time units in Excel, but you make it sound like it does it. But based on what you said it's doing, it seems like you should go to military time and maybe Excel does it that way.
    GSUdawg521 is offline   Reply With Quote
    Old 02-24-2006, 08:58 AM   #3
    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 TwoStep2888
    For a Psychology project I'm doing, I need to average a series of times people go to bed/wake up, and calculate the time between. For one of the groups I polled, several people answered 12:30 AM for their bedtime, but when I average that with the other times, those 12:30s bring the average time down by about two hours, because the 12:30s are AM, and the rest are PM. Is there any way to remedy this problem? Please help me.
    Would using military time help?
    rconverse is offline   Reply With Quote
    Old 02-24-2006, 09:10 AM   #4
    gbs3769
    Legen... ...dary!
     
    gbs3769's Avatar
     
    Join Date: Apr 2002
    Location: 315
    Posts: 28,759

    Shows Seen: 38

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse
    Would using military time help?


    - Primo
    __________________
    FY '09 | [HR]
    gbs3769 is offline   Reply With Quote
    Old 02-24-2006, 09:16 AM   #5
    GSUdawg521
    hbtd
     
    GSUdawg521's Avatar
     
    Join Date: Nov 2004
    Location: Southeast of disorder
    Posts: 25,114

    Shows Seen: 28

    DMB Hub Stubs: 11

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse
    Would using military time help?
    Yes, as would reading my post
    GSUdawg521 is offline   Reply With Quote
    Old 02-24-2006, 09:38 AM   #6
    TwoStep2888
     
    TwoStep2888's Avatar
     
    Join Date: Mar 2004
    Location: WI
    Posts: 71,084

    Shows Seen: 7

    DMB Hub Stubs: 8

    My Tour Central Stats

    Re: Excel Help

    Thanks. That helped alot.
    __________________
    trouble get behind me now, trouble let me be
    TwoStep2888 is offline   Reply With Quote
    Old 02-24-2006, 09:45 AM   #7
    mr.MikeD
    Heisenberg says "relax"
     
    mr.MikeD's Avatar
     
    Join Date: May 2004
    Posts: 7,745

    Shows Seen: 1

    DMB Hub Stubs: 3

    My Tour Central Stats

    Re: Excel Help

    i don't think just averaging using military time would help. before midnight would be ~2200 to 2400, after would something like 0001 to 0300. and the average bedtime would come out in midafternoon or something, which would be wrong. you might have to do something like set 0 as the lowest bedtime, and count up from there. find the average, and add it on to your 0 point to get the actual average.?
    or something
    mr.MikeD is offline   Reply With Quote
    Old 02-24-2006, 09:51 AM   #8
    mr.MikeD
    Heisenberg says "relax"
     
    mr.MikeD's Avatar
     
    Join Date: May 2004
    Posts: 7,745

    Shows Seen: 1

    DMB Hub Stubs: 3

    My Tour Central Stats

    Re: Excel Help

    say you had 5 people. 11 pm 11 pm 11 pm and 11 pm and 1 am.
    militarily you (23 X 4 + 1) / 5 =18.6 = ~6:40 pm. obviously wrong, cuz it smallerized you avg time instead of biggerizing it, like a later time should.

    so make 11 pm = 0. (0 X4 + 1 )/ 5 = 0.2 = 12 min.
    add 12 minutes to 0. average time is 11:12 pm.

    that might be right, or total shit.

    Last edited by mr.MikeD; 02-24-2006 at 09:52 AM.
    mr.MikeD is offline   Reply With Quote
    Old 02-24-2006, 10:26 AM   #9
    gbs3769
    Legen... ...dary!
     
    gbs3769's Avatar
     
    Join Date: Apr 2002
    Location: 315
    Posts: 28,759

    Shows Seen: 38

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    What you would do is set midnight to be your 0 time. Then if someone went to bed at 11, you would set their time to be -1. If someone went to bed at 1, it would be 1.

    Average that and you will get the offset from midnight that people went to bed.

    - Primo
    __________________
    FY '09 | [HR]
    gbs3769 is offline   Reply With Quote
    Old 02-24-2006, 10:32 AM   #10
    mr.MikeD
    Heisenberg says "relax"
     
    mr.MikeD's Avatar
     
    Join Date: May 2004
    Posts: 7,745

    Shows Seen: 1

    DMB Hub Stubs: 3

    My Tour Central Stats

    Re: Excel Help

    same as mine really.

    either way, do your own homework.
    mr.MikeD is offline   Reply With Quote
    Old 02-24-2006, 10:35 AM   #11
    gbs3769
    Legen... ...dary!
     
    gbs3769's Avatar
     
    Join Date: Apr 2002
    Location: 315
    Posts: 28,759

    Shows Seen: 38

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mr.MikeD
    same as mine really.

    either way, do your own homework.
    As long as in your case you set 10pm to be -1.

    But yes, you are correct.

    - Primo
    __________________
    FY '09 | [HR]
    gbs3769 is offline   Reply With Quote
    Old 02-24-2006, 10:36 AM   #12
    PilotC150
    Alpine Bound
     
    PilotC150's Avatar
     
    Join Date: Jul 2002
    Location: Twin Cities
    Posts: 19,981

    Shows Seen: 24

    DMB Hub Stubs: 12

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mr.MikeD
    same as mine really.

    either way, do your own homework.
    Yup, both ways work, but I believe Primo's method is a more proper method, because you can set midnight as your zero point, which will be unchanging, while if you use your earliest time as your zero point it will be dynamic depending on your data set.
    __________________
    "People ask us all the time...."What's your favorite place to play?" Now, you got your Gorges and you got your Red Rocks and so on, but there's an inexplicable magic that envelopes Alpine Valley. The band is the first to tell you how much they like coming back."
    PilotC150 is offline   Reply With Quote
    Old 02-24-2006, 10:36 AM   #13
    mr.MikeD
    Heisenberg says "relax"
     
    mr.MikeD's Avatar
     
    Join Date: May 2004
    Posts: 7,745

    Shows Seen: 1

    DMB Hub Stubs: 3

    My Tour Central Stats

    Re: Excel Help

    right. just need to set a reference of some kind.

    wow, this is not what i should be thinking about.

    i concede. primo's way is better. i am shamed.
    mr.MikeD is offline   Reply With Quote
    Old 02-24-2006, 10:40 AM   #14
    gbs3769
    Legen... ...dary!
     
    gbs3769's Avatar
     
    Join Date: Apr 2002
    Location: 315
    Posts: 28,759

    Shows Seen: 38

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by mr.MikeD
    i concede. primo's way is better. i am shamed.
    Don't be shamed. Your method was what reminded me how it should be done.

    - Primo
    __________________
    FY '09 | [HR]
    gbs3769 is offline   Reply With Quote
    Old 02-24-2006, 10:40 AM   #15
    gbs3769
    Legen... ...dary!
     
    gbs3769's Avatar
     
    Join Date: Apr 2002
    Location: 315
    Posts: 28,759

    Shows Seen: 38

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Regardless, we clearly would have been better students since we would have DONE OUR OWN FUCKING HOMEWORK.

    That is all.

    - Primo
    __________________
    FY '09 | [HR]
    gbs3769 is offline   Reply With Quote
    Old 03-03-2006, 10:07 AM   #16
    MacGuyver
    Richard D. Anderson
     
    MacGuyver's Avatar
     
    Join Date: Jul 2005
    Location: North Toronto
    Posts: 5,601

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    I have a new problem, hopefully people will help me out.

    I have a spreadsheet, with one value every 15 rows. so cells A1 - A14 are blank, and A15 has a value.

    then cells A16 to A29 are blank, and A30 has a value.

    and so on. this continues on for about 4000 rows. the values represent an average of 15 values.

    my question is:

    I need to plot a graph of these values (they are chemical concentrations if that matters) vs time. Thus, I need to make a table. Is there a way to transfer these values, that are spaced apart by 14 rows, into a table so that they are one below the other?

    I've tried some formulas and excel doesn't seem to be picking them up.

    I KNOW! Macguyver stumped. who'd of thunk??
    __________________
    Quote:
    Originally Posted by flyer3468
    You're MacGuyver, you can rig something up. A toothpick and can of axl grease and a rubber band, and you've got yourself an ultrasound machine.
    MacGuyver is offline   Reply With Quote
    Old 03-03-2006, 10:11 AM   #17
    The Onion
    Tired old rythm...
     
    Join Date: Dec 2004
    Location: NY
    Posts: 2,872

    Shows Seen: 137

    DMB Hub Stubs: 25

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by MacGuyver
    I have a new problem, hopefully people will help me out.

    I have a spreadsheet, with one value every 15 rows. so cells A1 - A14 are blank, and A15 has a value.

    then cells A16 to A29 are blank, and A30 has a value.

    and so on. this continues on for about 4000 rows. the values represent an average of 15 values.

    my question is:

    I need to plot a graph of these values (they are chemical concentrations if that matters) vs time. Thus, I need to make a table. Is there a way to transfer these values, that are spaced apart by 14 rows, into a table so that they are one below the other?

    I've tried some formulas and excel doesn't seem to be picking them up.

    I KNOW! Macguyver stumped. who'd of thunk??
    I'd copy the values into a single, simple list in another section of the spreadsheet, or on another page, and graph from that.

    I'm not all that bright when it comes to Microsoft Office, though, so I'm sure someone else will have a better answer.
    The Onion is offline   Reply With Quote
    Old 03-03-2006, 10:35 AM   #18
    PrettyPenelope
     
    Join Date: Mar 2004
    Location: Los Angeles, CA
    Posts: 1,455

    Shows Seen: 48

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by MacGuyver
    I have a new problem, hopefully people will help me out.

    I have a spreadsheet, with one value every 15 rows. so cells A1 - A14 are blank, and A15 has a value.

    then cells A16 to A29 are blank, and A30 has a value.

    and so on. this continues on for about 4000 rows. the values represent an average of 15 values.

    my question is:

    I need to plot a graph of these values (they are chemical concentrations if that matters) vs time. Thus, I need to make a table. Is there a way to transfer these values, that are spaced apart by 14 rows, into a table so that they are one below the other?

    I've tried some formulas and excel doesn't seem to be picking them up.

    I KNOW! Macguyver stumped. who'd of thunk??
    I would try making a copy of the sheet, and save it under another name so it doesn't affect the original. Then do a sort according to whatever column you want, so that all the lines with actual words/numbers will appear together at the top, and the blank lines below. Hope that helps.
    __________________

    PrettyPenelope is offline   Reply With Quote
    Old 03-03-2006, 10:38 AM   #19
    MacGuyver
    Richard D. Anderson
     
    MacGuyver's Avatar
     
    Join Date: Jul 2005
    Location: North Toronto
    Posts: 5,601

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by PrettyPenelope
    I would try making a copy of the sheet, and save it under another name so it doesn't affect the original. Then do a sort according to whatever column you want, so that all the lines with actual words/numbers will appear together at the top, and the blank lines below. Hope that helps.
    hmm a sort eh? didn't think of that. i'll try it.
    __________________
    Quote:
    Originally Posted by flyer3468
    You're MacGuyver, you can rig something up. A toothpick and can of axl grease and a rubber band, and you've got yourself an ultrasound machine.
    MacGuyver is offline   Reply With Quote
    Old 03-03-2006, 10:49 AM   #20
    thebigv
    Carpe Poon
     
    thebigv's Avatar
     
    Join Date: Nov 2002
    Location: Chicago/Southport Corridor
    Posts: 3,767

    Shows Seen: 10

    DMB Hub Stubs: 9

    My Tour Central Stats

    Re: Excel Help

    Download SPSS. You should be able to, no scratch that you can import any dbase file, especially Excel. It's a little more complicated at first glance, but the graphs and charts are much easier to manage. And when I say download, I mean visit spss.com and download the 14 day eval demo. I would not condone illegal download of any product, especially the product that I sell for a living.

    But honestly, easy user interface that many students use to put together a project quickly. Give it a shot.
    thebigv is offline   Reply With Quote
    Old 03-03-2006, 11:01 AM   #21
    MacGuyver
    Richard D. Anderson
     
    MacGuyver's Avatar
     
    Join Date: Jul 2005
    Location: North Toronto
    Posts: 5,601

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    But honestly, easy user interface that many students use to put together a project quickly. Give it a shot.
    translated


    But honestly, easy user interface that many students use to put together a project quickly. Give it a shot. and then have sex with it.

    __________________
    Quote:
    Originally Posted by flyer3468
    You're MacGuyver, you can rig something up. A toothpick and can of axl grease and a rubber band, and you've got yourself an ultrasound machine.
    MacGuyver is offline   Reply With Quote
    Old 03-03-2006, 11:05 AM   #22
    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 thebigv
    Download SPSS. You should be able to, no scratch that you can import any dbase file, especially Excel. It's a little more complicated at first glance, but the graphs and charts are much easier to manage. And when I say download, I mean visit spss.com and download the 14 day eval demo. I would not condone illegal download of any product, especially the product that I sell for a living.

    But honestly, easy user interface that many students use to put together a project quickly. Give it a shot.
    Nice sales pitch, homo.
    rconverse is offline   Reply With Quote
    Old 03-03-2006, 11:06 AM   #23
    MacGuyver
    Richard D. Anderson
     
    MacGuyver's Avatar
     
    Join Date: Jul 2005
    Location: North Toronto
    Posts: 5,601

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    shameless plug
    __________________
    Quote:
    Originally Posted by flyer3468
    You're MacGuyver, you can rig something up. A toothpick and can of axl grease and a rubber band, and you've got yourself an ultrasound machine.
    MacGuyver is offline   Reply With Quote
    Old 03-03-2006, 11:08 AM   #24
    BigE
     
    BigE's Avatar
     
    Join Date: Feb 2005
    Posts: 2,443

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by PrettyPenelope
    I would try making a copy of the sheet, and save it under another name so it doesn't affect the original. Then do a sort according to whatever column you want, so that all the lines with actual words/numbers will appear together at the top, and the blank lines below. Hope that helps.
    Create a reference column with ascending numbers to resort your data by once you've deleted the blanks. Otherwise when you sort your data column it'll put your sequence out of order.
    __________________
    Quote:
    Originally Posted by Hudak-Budak
    No cheese or sour cream? Come on, bud...go big or go home. Don't half-ass something as important as seeing how many tacos you can eat before you shit yourself.
    BigE is offline   Reply With Quote
    Old 03-03-2006, 11:14 AM   #25
    PrettyPenelope
     
    Join Date: Mar 2004
    Location: Los Angeles, CA
    Posts: 1,455

    Shows Seen: 48

    DMB Hub Stubs: 14

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by BigE
    Create a reference column with ascending numbers to resort your data by once you've deleted the blanks. Otherwise when you sort your data column it'll put your sequence out of order.
    Didn't think of that, but you're totally right.
    __________________

    PrettyPenelope is offline   Reply With Quote
    Old 03-03-2006, 11:14 AM   #26
    MacGuyver
    Richard D. Anderson
     
    MacGuyver's Avatar
     
    Join Date: Jul 2005
    Location: North Toronto
    Posts: 5,601

    Shows Seen: 0

    DMB Hub Stubs: 1

    My Tour Central Stats

    Re: Excel Help

    sorting totally worked guys. I knew it was something basic but couldn't figure it out... thanks!
    __________________
    Quote:
    Originally Posted by flyer3468
    You're MacGuyver, you can rig something up. A toothpick and can of axl grease and a rubber band, and you've got yourself an ultrasound machine.
    MacGuyver is offline   Reply With Quote
    Old 03-03-2006, 11:32 AM   #27
    thebigv
    Carpe Poon
     
    thebigv's Avatar
     
    Join Date: Nov 2002
    Location: Chicago/Southport Corridor
    Posts: 3,767

    Shows Seen: 10

    DMB Hub Stubs: 9

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse
    Nice sales pitch, homo.

    Thank you. Go masterbate to an autographed picture of Chesty McSweatervest handing a new recruit the keys to an Escalade.
    thebigv is offline   Reply With Quote
    Old 03-03-2006, 11:33 AM   #28
    thebigv
    Carpe Poon
     
    thebigv's Avatar
     
    Join Date: Nov 2002
    Location: Chicago/Southport Corridor
    Posts: 3,767

    Shows Seen: 10

    DMB Hub Stubs: 9

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by MacGuyver
    shameless plug
    Shameless? yes. Worthless? yes. I don't make anything for promoting my product except making myself feel like I did something at work today.
    thebigv is offline   Reply With Quote
    Old 03-03-2006, 11:35 AM   #29
    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 thebigv
    Thank you. Go masterbate to an autographed picture of Chesty McSweatervest handing a new recruit the keys to an Escalade.
    That's funny. I do have an autographed picture.
    rconverse is offline   Reply With Quote
    Old 03-03-2006, 11:36 AM   #30
    thebigv
    Carpe Poon
     
    thebigv's Avatar
     
    Join Date: Nov 2002
    Location: Chicago/Southport Corridor
    Posts: 3,767

    Shows Seen: 10

    DMB Hub Stubs: 9

    My Tour Central Stats

    Re: Excel Help

    Quote:
    Originally Posted by rconverse
    That's funny. I do have an autographed picture.
    But it's not keys to an Escalade, is it? Is it the rights to Voyd Carr's anus?
    thebigv 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 01:05 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