Spreadsheet Formula Help

Discussion in 'Computer Support' started by Steve Freides, Feb 14, 2009.

  1. I created a table for myself in FoxPro then built some simple SQL
    statements to give me tallies of various things. I would like to give
    this to someone as a spreadsheet so that the data can be changed by them
    and the formulas still give results. Here's the structure of the
    table - it's basically a course listing from my department

    DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
    DUP_NUMBER

    Department is the same for everything right now but I have a second
    department to add.

    The Duplicate field is either True or False - sometimes two course
    numbers actually refer to the same teacher, place, and time, e.g., the
    chorus - you sign up for chorus 1, chorus 2, chorus 3, or chorus 4,
    depending on how many times you've taken it before, but for these
    purposes, it should only count as one course.

    The purpose here is to compare what full- and part-time faculty teach.
    The spreadsheet format is universal enough that the spreadsheet can be,
    e.g., given to the higher-ups at the college as part of trying to win
    more full-time faculty lines. The multiple departments are intended to
    show which departments need full-time lines the most, all other things
    being equal.

    Here are two queries that yield useful results:

    * credits taught by part- and full-time
    SELECT fulltime, SUM(credits) FROM Spring09
    WHERE NOT duplicate
    GROUP BY fulltime ORDER BY fulltime

    * sections taught by part- and full-time
    SELECT fulltime, COUNT(*) FROM Spring09
    WHERE NOT duplicate
    GROUP BY fulltime ORDER BY fulltime

    With all this now in a spreadsheet, is there a way in a formula to get
    the kind of results the query yields? At the moment each field is now a
    column, e.g., faculy name is column D.

    Thanks very much in advance for any assistance.

    -S-
    Steve Freides, Feb 14, 2009
    #1
    1. Advertising

  2. Steve Freides

    why? Guest

    On Fri, 13 Feb 2009 21:00:23 -0500, Steve Freides wrote:

    >I created a table for myself in FoxPro then built some simple SQL


    That's going back a bit, assume you mean the MS Visual FoxPro not so
    nice version :) as opposed to the very nice original FP.

    >statements to give me tallies of various things. I would like to give
    >this to someone as a spreadsheet so that the data can be changed by them


    Going from a DB to SS, is generally the wrong way round. Lots of SS
    should be a DB.

    >and the formulas still give results. Here's the structure of the
    >table - it's basically a course listing from my department
    >
    >DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
    >DUP_NUMBER
    >
    >Department is the same for everything right now but I have a second
    >department to add.


    You aren't going to get the schema / requirements understood this way,
    could have skipped all this bit :)

    >The Duplicate field is either True or False - sometimes two course
    >numbers actually refer to the same teacher, place, and time, e.g., the


    <big snip>

    >Here are two queries that yield useful results:


    For someone....

    >* credits taught by part- and full-time
    >SELECT fulltime, SUM(credits) FROM Spring09
    >WHERE NOT duplicate
    >GROUP BY fulltime ORDER BY fulltime
    >
    >* sections taught by part- and full-time
    >SELECT fulltime, COUNT(*) FROM Spring09
    >WHERE NOT duplicate
    >GROUP BY fulltime ORDER BY fulltime
    >
    >With all this now in a spreadsheet, is there a way in a formula to get
    >the kind of results the query yields? At the moment each field is now a


    I can't see what those results would be.

    >column, e.g., faculy name is column D.


    Depending on which unnamed SS you are using, most have some sort of
    macro language you could use to get the same results.

    >Thanks very much in advance for any assistance.


    Leave it as a DB.

    Me
    why?, Feb 14, 2009
    #2
    1. Advertising

  3. Steve Freides

    Steve Guest

    Spreadsheets normally have formulas which sum columns where the
    records summed are limited (i.e. filtered) by the value in some other
    column. For example, SUMIF. The exact synatax of the formula will
    depend on the brand of spreadsheet you are using. It may actually
    require some digging into the help section of the spreadsheet you are
    using. HTH
    Steve


    On Fri, 13 Feb 2009 21:00:23 -0500, "Steve Freides" <>
    wrote:

    >I created a table for myself in FoxPro then built some simple SQL
    >statements to give me tallies of various things. I would like to give
    >this to someone as a spreadsheet so that the data can be changed by them
    >and the formulas still give results. Here's the structure of the
    >table - it's basically a course listing from my department
    >
    >DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
    >DUP_NUMBER
    >
    >Department is the same for everything right now but I have a second
    >department to add.
    >
    >The Duplicate field is either True or False - sometimes two course
    >numbers actually refer to the same teacher, place, and time, e.g., the
    >chorus - you sign up for chorus 1, chorus 2, chorus 3, or chorus 4,
    >depending on how many times you've taken it before, but for these
    >purposes, it should only count as one course.
    >
    >The purpose here is to compare what full- and part-time faculty teach.
    >The spreadsheet format is universal enough that the spreadsheet can be,
    >e.g., given to the higher-ups at the college as part of trying to win
    >more full-time faculty lines. The multiple departments are intended to
    >show which departments need full-time lines the most, all other things
    >being equal.
    >
    >Here are two queries that yield useful results:
    >
    >* credits taught by part- and full-time
    >SELECT fulltime, SUM(credits) FROM Spring09
    >WHERE NOT duplicate
    >GROUP BY fulltime ORDER BY fulltime
    >
    >* sections taught by part- and full-time
    >SELECT fulltime, COUNT(*) FROM Spring09
    >WHERE NOT duplicate
    >GROUP BY fulltime ORDER BY fulltime
    >
    >With all this now in a spreadsheet, is there a way in a formula to get
    >the kind of results the query yields? At the moment each field is now a
    >column, e.g., faculy name is column D.
    >
    >Thanks very much in advance for any assistance.
    >
    >-S-
    >
    Steve, Feb 14, 2009
    #3
  4. Steve Freides

    Mac Guest

    You don't need the order by clause as the group by already sorts. Also using
    a separate table name for each year is poor practise (not normalized) add a
    column called year and select where year = nn then you only need the one
    table


    "Steve Freides" <> wrote in message
    news:...
    >I created a table for myself in FoxPro then built some simple SQL
    >statements to give me tallies of various things. I would like to give this
    >to someone as a spreadsheet so that the data can be changed by them and the
    >formulas still give results. Here's the structure of the table - it's
    >basically a course listing from my department
    >
    > DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
    > DUP_NUMBER
    >
    > Department is the same for everything right now but I have a second
    > department to add.
    >
    > The Duplicate field is either True or False - sometimes two course numbers
    > actually refer to the same teacher, place, and time, e.g., the chorus -
    > you sign up for chorus 1, chorus 2, chorus 3, or chorus 4, depending on
    > how many times you've taken it before, but for these purposes, it should
    > only count as one course.
    >
    > The purpose here is to compare what full- and part-time faculty teach. The
    > spreadsheet format is universal enough that the spreadsheet can be, e.g.,
    > given to the higher-ups at the college as part of trying to win more
    > full-time faculty lines. The multiple departments are intended to show
    > which departments need full-time lines the most, all other things being
    > equal.
    >
    > Here are two queries that yield useful results:
    >
    > * credits taught by part- and full-time
    > SELECT fulltime, SUM(credits) FROM Spring09
    > WHERE NOT duplicate
    > GROUP BY fulltime ORDER BY fulltime
    >
    > * sections taught by part- and full-time
    > SELECT fulltime, COUNT(*) FROM Spring09
    > WHERE NOT duplicate
    > GROUP BY fulltime ORDER BY fulltime
    >
    > With all this now in a spreadsheet, is there a way in a formula to get the
    > kind of results the query yields? At the moment each field is now a
    > column, e.g., faculy name is column D.
    >
    > Thanks very much in advance for any assistance.
    >
    > -S-
    >
    Mac, Feb 15, 2009
    #4
  5. Steve Freides

    why? Guest

    On Sun, 15 Feb 2009 08:47:52 GMT, Mac wrote:

    >You don't need the order by clause as the group by already sorts. Also using
    >a separate table name for each year is poor practise (not normalized) add a
    >column called year and select where year = nn then you only need the one
    >table


    Doesn't sort of fit the OP's going from a DB to a SS. It's often enough
    hard work doing the normalization in the first place without undoing it
    for a SS :) I managed to skip mentioning it the 1st time.

    >
    >"Steve Freides" <> wrote in message
    >news:...
    >>I created a table for myself in FoxPro then built some simple SQL
    >>statements to give me tallies of various things. I would like to give this
    >>to someone as a spreadsheet so that the data can be changed by them and the
    >>formulas still give results. Here's the structure of the table - it's
    >>basically a course listing from my department
    >>
    >> DEPARTMENT COURSE SECTION FACULTY CREDITS FULLTIME DUPLICATE
    >> DUP_NUMBER
    >>
    >> Department is the same for everything right now but I have a second
    >> department to add.

    <snip>

    Me
    why?, Feb 15, 2009
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jeaneie

    formula for my spreadsheet

    Jeaneie, Nov 30, 2004, in forum: Computer Support
    Replies:
    5
    Views:
    1,543
    dadiOH
    Nov 30, 2004
  2. *

    Re: Excel formula help

    *, Apr 2, 2005, in forum: Computer Support
    Replies:
    4
    Views:
    688
    Buckaroo Banzai
    Apr 3, 2005
  3. John

    Excel formula help

    John, Apr 2, 2005, in forum: Computer Information
    Replies:
    10
    Views:
    640
    Buckaroo Banzai
    Apr 3, 2005
  4. BIGEYE

    Excel Spreadsheet Formula

    BIGEYE, Nov 14, 2005, in forum: Computer Support
    Replies:
    14
    Views:
    1,598
    Blinky the Shark
    Nov 14, 2005
  5. SS
    Replies:
    5
    Views:
    1,037
    Toolman Tim
    Jul 9, 2006
Loading...

Share This Page