The Excel Questions Thread

[dsgNYC]

Originally Posted by calatrava

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.

 

[calatrava]

Originally Posted by dsgNYC

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

 

[dsgNYC]

Originally Posted by calatrava

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?

 

[calatrava]

Originally Posted by dsgNYC

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.

 

[vinouspleasure]

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.

 

[dsgNYC]

Originally Posted by calatrava

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.

Originally Posted by vinouspleasure

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.

 

[Davidko19]

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)

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

 

[dsgNYC]

Originally Posted by Davidko19

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)

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

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
​

 

[cromulated]

Originally Posted by Davidko19

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)

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

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

 

[dsgNYC]

^^ 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.

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

 

[haganah]

nvm

 

[CunningSmeagol]

Maybe this will do better here...sigh

 

[dsgNYC]

Originally Posted by CunningSmeagol

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.

 

[Davidko19]

THANKS!!!! YOU DUDES ROCK.

I need to get a book on this ****!

 

[CunningSmeagol]

Originally Posted by dsgNYC

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.