Boring Exceltrackworld question

Home Forum Chat Forum Boring Exceltrackworld question

Viewing 10 posts - 1 through 10 (of 10 total)
  • Boring Exceltrackworld question
  • Premier Icon AndyRT
    Subscriber

    You could use a pivot table.

    That way you can isolate a variable to find trends easily without having to do any Visual Basic.

    As long as you have saved the spreadsheet you can go to Insert, Pivot table, do it on a new worksheet and then drag and drop the variables to the table.

    it may help to check out Exel help first. Once you get the hang of Pivot tables you will wonder how you ever worked without them.

    mattyd
    Member

    Yep, a second vote for a pivot table.

    Premier Icon annebr
    Subscriber

    =sumif(range,criteria,[sumrange])

    mogrim
    Member

    3rd vote!

    Easiest way is:

    1. Select all the data you want to use, including the column headers

    2. Click on the convert to table button (on the main ribbon)

    3. Select insert ribbon, then click on insert pivot table. Select the fields you want to use.

    brakes
    Member

    I’d use sumif because I hate pivot tables.
    you’ll need a column of the lecturers names though to refer to – they are the ‘criteria’ in the formula posted by annebr, range is the table you’ve posted above and sum range is the column of numbers.

    austin
    Member

    Thanks, I’ll look into pivot tables. Am I right in thinking that if I used sumif, I’d have to use a different formula for each teacher name?

    Premier Icon annebr
    Subscriber

    yes that’s right you have a row per teacher name and that formula by each name

    austin
    Member

    Please Excel nerds, can you tell me how to get the sum of all values in one column which have the same value in another column? Lecturers have students in different classes and I want to know how many students they have in total. e.g.

    Name | Number of Students
    Smith | 20
    Smith | 25
    Jones | 15
    Jones | 10
    Jones | 7
    Brown | 5
    Brown | 20

    Is it possible to have a single formula in a column which will do this? There are about 380 staff teaching about 2,000 classes.

    jfletch
    Member

    You don’t need a different formula if you go Sumif. You just need to have the correct cell references in each formula which can be achived by careful application of a few $ signs.

    I’d go pivot for either a one off data analysis or if the teachers names change a lot. I’d go sumif if the teachers names are relatively stable but I want the spreadhseet to be a long standing tool.

    Pivot tabels are useful for quick analysis but a bit clunky to use all the time as they tend to lose formatting and be a bit funny with links elsewhere.

    austin
    Member

    PIVOT TABLES TO THE RESCUE! Thanks for your help

Viewing 10 posts - 1 through 10 (of 10 total)

The topic ‘Boring Exceltrackworld question’ is closed to new replies.