The Excel Questions Thread

Discussion in 'General Chat' started by CunningSmeagol, May 4, 2009.

  1. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Thanks a lot, I was thinking that OFFSET was the function that I needed and it worked. Just wondering if anyone knows how to create cumulative totals of say production from the same company over time?

    A SUMIF should work, if the company's name is the only criteria for the summation.
     


  2. calatrava

    calatrava Active Member

    Messages:
    43
    Likes Received:
    0
    Joined:
    Oct 10, 2006
    A SUMIF should work, if the company's name is the only criteria for the summation.

    Yep, it did. For reference

    =SUMIF(INDIRECT("A2:A"&ROW()),"="&A2,INDIRECT("B2:B"&ROW()))

    For data like this:

    1 a b c
    2 Category Value Cumulative Formula
     


  3. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Yep, it did. For reference

    =SUMIF(INDIRECT("A2:A"&ROW()),"="&A2,INDIRECT("B2:B"&ROW()))

    For data like this:

    1 a b c
    2 Category Value Cumulative Formula


    Very nice, that's a pretty formula. Quick thought - It looks like that formula is only summing from the cell the formula is in to the top of the worksheet. Wouldn't you want to sum the entire dataset, from A2 to the bottom? How is your worksheet set up?
     


  4. calatrava

    calatrava Active Member

    Messages:
    43
    Likes Received:
    0
    Joined:
    Oct 10, 2006
    Very nice, that's a pretty formula. Quick thought - It looks like that formula is only summing from the cell the formula is in to the top of the worksheet. Wouldn't you want to sum the entire dataset, from A2 to the bottom? How is your worksheet set up?

    Basically i have a bunch of company tickers in column A, year in column B, production in column C. What I did was change that formula from above a bit and copy downwards in column D. What it does is sum it all up and report the cumulative total. I ended up pasting a list of tickers at the end of column A to get their cumulative totals easily, which appear to the right. There is probably a better way of doing this, but it works for now. But a part of me hates an ad hoc solution rather than one that works for all situations.
     


  5. vinouspleasure

    vinouspleasure Senior member

    Messages:
    1,310
    Likes Received:
    20
    Joined:
    Jul 16, 2007
    take a look at array formulas. I really haven't followed your example, but I found array formulas to be superior to countifs and sumifs. We were creating daily metric dashboards and we went from about 3 hours/per day to 20 minutes day for our dashboard.

    You have to remember to hit ctrl, shift and enter.
     


  6. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Basically i have a bunch of company tickers in column A, year in column B, production in column C. What I did was change that formula from above a bit and copy downwards in column D. What it does is sum it all up and report the cumulative total. I ended up pasting a list of tickers at the end of column A to get their cumulative totals easily, which appear to the right. There is probably a better way of doing this, but it works for now. But a part of me hates an ad hoc solution rather than one that works for all situations.

    It sounds like you could just use a PivotTable instead of the SUMIF formulas. That way you'd catch any new company tickers that show up in your report that you didn't manually paste below the report. If you don't want to use a Pivot, I'd suggest moving your list of tickers to another worksheet. In this case, you'll have to modify the formulas, so if you run into any troubles just come back here and ask.

    If you decide to go the Pivot route, make sure the Pivot range extends far below the number of rows in your normal reports, that way you won't have you update the Pivot range again. Sometimes I just capture every row in each column of my dataset for the Pivot, but that bloats the size of your file. Then, you could set a macro that refreshes your Pivot each time the file is opened, saved, etc.



    take a look at array formulas. I really haven't followed your example, but I found array formulas to be superior to countifs and sumifs. We were creating daily metric dashboards and we went from about 3 hours/per day to 20 minutes day for our dashboard.

    You have to remember to hit ctrl, shift and enter.


    SUMIF and COUNTIF are array formulas - they're just a lot simpler than an array formula you'd write if you had more than 1 criteria.
     


  7. Davidko19

    Davidko19 Senior member

    Messages:
    2,411
    Likes Received:
    5
    Joined:
    Aug 6, 2008
    I need a formula that will add up some conditional results based on the Yes answer of 200+ people from about 20 different departments.

    A sample of the data is below, basically what I need it to do is add up all the YES answers based on peoples channels so I can see what percentage of that department answered YES and put that into a chart. The data pull only has YES or an empty square.

    Ultimately the formula should read "Out of 2 corp comm employees, 1 answered yes", "Out of 1 creative services employee, 0 answered yes", "Out of 1 customer care employees, 1 answered yes" Then I can use that data to make a chart and percentages.

    By the way, I am using the COUNTIF function to add up the number of employees/department in Column B, but this has one more condition in it. I think the formula should look something like: =sum(B1:B8, if=B1)-(c1:c8, if=B1)

    [​IMG]


    Any ideas??? Keep in mind, Ill have to export this to another page to create a pretty chart [​IMG]
     


  8. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    I need a formula that will add up some conditional results based on the Yes answer of 200+ people from about 20 different departments. A sample of the data is below, basically what I need it to do is add up all the YES answers based on peoples channels so I can see what percentage of that department answered YES and put that into a chart. The data pull only has YES or an empty square. Ultimately the formula should read "Out of 2 corp comm employees, 1 answered yes", "Out of 1 creative services employee, 0 answered yes", "Out of 1 customer care employees, 1 answered yes" Then I can use that data to make a chart and percentages. By the way, I am using the COUNTIF function to add up the number of employees/department in Column B, but this has one more condition in it. I think the formula should look something like: =sum(B1:B8, if=B1)-(c1:c8, if=B1) [​IMG] Any ideas??? Keep in mind, Ill have to export this to another page to create a pretty chart [​IMG]
    This should be pretty simple to make a data table that will count what you're looking for as well as be the source for a pretty graph. I'd start the table with 3 columns - Department & Employees Responding Yes & Total Employees. In the cells below Department you'd list out all of the unique departments. In the cells below Employees Responding Yes you'd use an array formula like the following: =SUM(IF(Department=$A2,IF(Response="YES",1,0))) Keep in mind the above formula is an array formula, so you'll have to hit Shift+Ctrl+Enter at the same time while your cursor is blinking in the cell. Under Total Employees you can use the following formula: =COUNTIF(Department,$A2) In the formulas above, Department would be the range in your dataset that contains the employees department and Response would be the range where their answer is located. Keep in mind, this is assuming all of your data is in one table. Once you get the formulas to work for the first department, you can copy it down so that it fills in the cells corresponding to all departments. I'm thinking it'd look like the following:
    Code:

    Department #Yes Total
    Sales 1 2
    Accounting 2 2
     


  9. cromulated

    cromulated Senior member

    Messages:
    146
    Likes Received:
    0
    Joined:
    Oct 25, 2008
    Location:
    toronto, canada
    I need a formula that will add up some conditional results based on the Yes answer of 200+ people from about 20 different departments.

    A sample of the data is below, basically what I need it to do is add up all the YES answers based on peoples channels so I can see what percentage of that department answered YES and put that into a chart. The data pull only has YES or an empty square.

    Ultimately the formula should read "Out of 2 corp comm employees, 1 answered yes", "Out of 1 creative services employee, 0 answered yes", "Out of 1 customer care employees, 1 answered yes" Then I can use that data to make a chart and percentages.

    By the way, I am using the COUNTIF function to add up the number of employees/department in Column B, but this has one more condition in it. I think the formula should look something like: =sum(B1:B8, if=B1)-(c1:c8, if=B1)

    [​IMG]


    Any ideas??? Keep in mind, Ill have to export this to another page to create a pretty chart [​IMG]


    how about this (column D is just a text string of columns B & C):

    [​IMG]
     


  10. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    ^^ That's why I love Excel - There are so many ways to solve one problem.

    So, I have a lot of free time and threw together a sample of what I'd do for your project.

    Here's a screenshot I took of the simple model I put together that can act as a datatable in a presentation as well as the source for a graph.

    [​IMG]

    * I forgot to delete the "Department Employees" label in the graph's legend, so you can pretend like it's not there.
     


  11. haganah

    haganah Senior member

    Messages:
    6,351
    Likes Received:
    27
    Joined:
    Nov 24, 2007
    Location:
    New York, NY


  12. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    Maybe this will do better here...sigh
     


  13. dsgNYC

    dsgNYC Senior member

    Messages:
    422
    Likes Received:
    0
    Joined:
    Dec 9, 2008
    Maybe this will do better here...sigh

    I had a coworker to created an Excel workbook to randomly pick a restaurant for lunch. You press a button and about 50 restaurants scroll through one cell and it lands on one after a predetermined amount of time. I'll have to ask him for it and post it here - it's pretty cool.
     


  14. Davidko19

    Davidko19 Senior member

    Messages:
    2,411
    Likes Received:
    5
    Joined:
    Aug 6, 2008
    THANKS!!!! YOU DUDES ROCK. [​IMG]

    I need to get a book on this shit![​IMG]
     


  15. CunningSmeagol

    CunningSmeagol Senior member

    Messages:
    3,933
    Likes Received:
    17
    Joined:
    Oct 15, 2006
    Location:
    New York
    I had a coworker to created an Excel workbook to randomly pick a restaurant for lunch. You press a button and about 50 restaurants scroll through one cell and it lands on one after a predetermined amount of time. I'll have to ask him for it and post it here - it's pretty cool.

    lol. For more realism, I would modify 40 of those choices to read "cafeteria downstairs." The rest would be Indian buffets.
     


Share This Page

Styleforum is proudly sponsored by