Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Computing > Computer Support > Spreadsheet Formula Help

Reply
Thread Tools

Spreadsheet Formula Help

 
 
Steve Freides
Guest
Posts: n/a
 
      02-14-2009
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-


 
Reply With Quote
 
 
 
 
why?
Guest
Posts: n/a
 
      02-14-2009

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
 
Reply With Quote
 
 
 
 
Steve
Guest
Posts: n/a
 
      02-14-2009
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" <(E-Mail Removed)>
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-
>


 
Reply With Quote
 
Mac
Guest
Posts: n/a
 
      02-15-2009
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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-
>



 
Reply With Quote
 
why?
Guest
Posts: n/a
 
      02-15-2009

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" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>>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
 
Reply With Quote
 
 
 
Reply

Thread Tools

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 On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Spreadsheet Formula BIGEYE Computer Support 14 11-14-2005 11:55 PM
Re: Excel formula help * Computer Support 4 04-02-2005 11:03 PM
Rating formula? Help apreciated! alex ASP .Net 2 03-08-2005 01:14 AM
formula for my spreadsheet Jeaneie Computer Support 5 11-30-2004 01:53 PM
Spreadsheet::WriteExcel, Excel formula won't calculate Sven Jungnickel Perl Misc 3 07-28-2004 11:18 PM



Advertisments