Excel

Okay . . . let's try this again.

Moderators: Shirley, Sabo, brian, rass, DaveInSeattle

User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Lets say the cities are in column D, you'd copy column D into a new column (pretend its column G) then go to Data > Remove Duplicates to create your list of unique cities. Then in Column H next to each city, you'd do =COUNTIF(D:D,H1) and H2, H3, H4 ... on down. The graph would run off G-H.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

The only real issue would be some new-to-the-list city being added after the summary/graph are created.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

mister d wrote: Tue Oct 16, 2018 4:03 pm The only real issue would be some new-to-the-list city being added after the summary/graph are created.
That's not a concern since the data set is final. So this helps. I'll play around and see if I can't figure it out. Thanks.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

I can email an example later on if you need it.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

mister d wrote: Tue Oct 16, 2018 4:07 pm I can email an example later on if you need it.
No that worked perfectly. And I don't even need the graph I just need the numbers for each city so that was easier than I would have guessed. I owe you a beer.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

brian wrote: Tue Oct 16, 2018 3:58 pm
mister d wrote: Tue Oct 16, 2018 3:57 pm Would building a summary section, using COUNTIF and running the graph off there count as manual?
No, I meant literally manual. Like all I would know how to do is sort by that column and then "tally" up the results. So anything that involves even a few steps is fine.
A simple Pivot chart should do this.
You could also do a Pivot Table and then copy the table and use the graph feature.
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

Yeah, I need to learn more about pivot tables. Mr. D's suggestion worked fine but it didn't look great, but for these customers I don't care because they're assholes. But for customer I like I might want to jazz it up a bit.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

If the list doesn’t look pretty you didn’t make a pretty list.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

mister d wrote: Tue Oct 16, 2018 7:55 pm If the list doesn’t look pretty you didn’t make a pretty list.
Yeah I should say I could have put more work into presentation but that’s on me.
Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

If you want to make pretty presentations and charts I highly suggest ‘think-cell’. It’s a German software which piggy backs off of both excel and PowerPoint. They’ve done a fantastic job of integrating the two (much better than Microsoft), plus their graphical program is much more easily manipulated to make very pretty charts. You can also easily add additional bells and whistles to your charts to show things like peak to peak differences (and report the difference as an absolute or a %), etc.

It’s a great program and it can make your presentations stand out from the typical.
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

brian wrote: Tue Oct 16, 2018 3:55 pm Let's say you're hypothetically dealing with a bunch of lazy Canadians.

And let's say you're providing them data in an Excel file that lists a city for each person in a column and there are 25,000 rows. (There's other data in there, but they don't care about that.)

How could you use Excel to list the number of times each city appears in the report in a graph or some other way to represent how many times each city is represented without having to compile it manually?

For example:

Toronto - 352
Oshawa - 283
Thunder Bay - 218

etc., etc.
Pivot table definitely the easiest way. Highlight all of your cities in the raw data, including a column header (‘cities’ for the example below. Go -> Data -> Insert -> Pivot Table. Click through and let it create a new sheet. When the pivot table template shows up, drag your cities header rectangle block down into the summary box (bottom right) and then click on that block to make sure it’s on ‘count’. It should show you exactly what you want. You can sum, average, stdev, plus a lot of other functions. You can also sum data related to the cities data if you wanted. If say you had additional data in different columns in your raw data table, you can drag the cities block to the rows box (bottom left), and then pull whatever other variables you want to the sum box and pick your calculations.

Pivot tables are extremely useful for parsing big data sets into more manageable data. They are also really useful for grouping time based data into days/weeks/months/years, etc.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

Square Rob wrote: Wed Oct 17, 2018 5:18 am
brian wrote: Tue Oct 16, 2018 3:55 pm Let's say you're hypothetically dealing with a bunch of lazy Canadians.

And let's say you're providing them data in an Excel file that lists a city for each person in a column and there are 25,000 rows. (There's other data in there, but they don't care about that.)

How could you use Excel to list the number of times each city appears in the report in a graph or some other way to represent how many times each city is represented without having to compile it manually?

For example:

Toronto - 352
Oshawa - 283
Thunder Bay - 218

etc., etc.
Pivot table definitely the easiest way. Highlight all of your cities in the raw data, including a column header (‘cities’ for the example below. Go -> Data -> Insert -> Pivot Table. Click through and let it create a new sheet. When the pivot table template shows up, drag your cities header rectangle block down into the summary box (bottom right) and then click on that block to make sure it’s on ‘count’. It should show you exactly what you want. You can sum, average, stdev, plus a lot of other functions. You can also sum data related to the cities data if you wanted. If say you had additional data in different columns in your raw data table, you can drag the cities block to the rows box (bottom left), and then pull whatever other variables you want to the sum box and pick your calculations.

Pivot tables are extremely useful for parsing big data sets into more manageable data. They are also really useful for grouping time based data into days/weeks/months/years, etc.
And the newest version has a "Recommend Pivot Table" feature that is also good for getting your started.
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

OK so my father has a spreadsheet where he tracks all the golfers in his community. His job is to coordinate the Saturday start times.

Probably 20 golfers. Any given Saturday there’s probably a dozen golfers who need a tee time. He wants to setup a formula that, when there’s a ‘1’ in a golfer’s row, then that golfer’s email address (found in another column) will append to a list of email addresses. He’d then copy that cell and dump it in an email to announce their times, etc.

Say, column A is the name, B is the email address, and C is the ‘1’ or ‘0’ if he’s golfing that Saturday.

Is this concatenation? Appending strings?

Let’s cut to the chase: how the hell do I do this?
Noli Timere Messorem
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

sounds like a conditional statement, if status = 1 then give him a tee time. do you need another column with the tee times? pairings, threesomes?
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

degenerasian wrote: Fri Dec 14, 2018 2:06 pm sounds like a conditional statement, if status = 1 then give him a tee time. do you need another column with the tee times? pairings, threesomes?
No nothing that complicated.

So how would the formula look to append a list? All apologies here - I haven’t dealt in Excel in about 10-12 years.
Noli Timere Messorem
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

So you’d select all the email addresses in the email column, and then something like IF “1” in column C, then place email in a string in the target cell?

Sounds good. But no idea how to build it.
Noli Timere Messorem
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

I’m doing some digging...

https://www.howtoexcel.org/formulas/how ... e-formula/

Is this what I want?
Noli Timere Messorem
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

That might work, i'm trying to find an easier way
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
User avatar
degenerasian
The Dude
Posts: 12302
Joined: Tue Mar 12, 2013 12:22 pm

Re: Excel

Post by degenerasian »

I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.
Kung Fu movies are like porn. There's 1 on 1, then 2 on 1, then a group scene..
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

degenerasian wrote: Fri Dec 14, 2018 4:02 pm I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.
Thank you. I’ll give this a shot.
Noli Timere Messorem
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

degenerasian wrote: Fri Dec 14, 2018 4:02 pm I think this will work

column A is the name, B is the email address, and C is the ‘1’ or ‘0’

In column D put in the first row =IF(C1=1,B1&"")
then in the second row put in =IF(C2=1,D1&" "&B2,D1)

from cell D2 drag the formula down to the bottom of your list, the last cell should be all the emails you want.
This is great! How would we build this string to delimit the values with a comma?
Noli Timere Messorem
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

=if(C1=0,"",D1&",")&if(C2=0,"",D2&",")&if(D3= ... until the end. It'll build it out into a single cell skipping all of the column D cells that don't have emails.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

I was thinking a series of if statements but I got distracted and didn’t come back to it.
You know what you need? A lyrical sucker punch to the face.
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

Thanks guys. We just added a comma in the cell after the email address and that did the trick.
Noli Timere Messorem
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Next level excel is oversolving your problems to avoid any manual fixes at all.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

mister d wrote: Sat Dec 15, 2018 9:00 pm Next level excel is oversolving your problems to avoid any manual fixes at all.
I have definitely spent a day gettin a furmila right when I could have done same with simple math in an hour.
You know what you need? A lyrical sucker punch to the face.
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

OK, so let's say you have a spreadsheet chart where you're attempting to show revenue by quarter over a 3 year period, but for four different products. What's the most effective chart to show this? I want to say Pivot Chart where you could just activate different years, but what about a scatter chart for all 3 years in one chart? With 12 data points each, is that overkill?
Noli Timere Messorem
teeteebee
Brandt
Posts: 366
Joined: Sun Mar 17, 2013 6:50 am

Re: Excel

Post by teeteebee »

A bar graph could work. With each qtr broken down into four colored bars. Each quarter shows the change by product.
oh shit...
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

teeteebee wrote: Sun Jan 27, 2019 5:15 pm A bar graph could work. With each qtr broken down into four colored bars. Each quarter shows the change by product.
Yeah. I can see that. But that's 12 data points each. Is that sensory overload?

Might you use a table with embedded charts for each product?
Noli Timere Messorem
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
teeteebee
Brandt
Posts: 366
Joined: Sun Mar 17, 2013 6:50 am

Re: Excel

Post by teeteebee »

mister d wrote: Sun Jan 27, 2019 7:04 pm Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)
Stacked bar chart would look good. 48 narrow bars wouldn’t be too bad. Believe I used something similar before - for regional analysis by month.
oh shit...
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

mister d wrote: Sun Jan 27, 2019 7:04 pm Stacked bar chart?


(It’s somewhat limited in you can only really sight compare the bottom product and overall, but it’s cleaner than 48+ bars.)
Maybe I'm just thinking too linear, but how would 12 stacked bars be more convincing than 4 lines?
Noli Timere Messorem
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Just depends on what you want to show I guess? If total revenue isn’t a factor, 4 lines works. If total revenue matters and you want to show components, the stacked bar is probably better.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
Shirley
The Dude
Posts: 7516
Joined: Mon Mar 11, 2013 2:32 pm

Re: Excel

Post by Shirley »

Yeah, it all depends on the goal of the chart. If each product needs to be tracked separately, then a grouped bar chart is probably best. If it's total revenue that's most important, but you still want to see the contribution of each product, then a stacked bar chart is better.
Totally Kafkaesque
User avatar
Square Rob
Jesus Quintana
Posts: 769
Joined: Thu Mar 14, 2013 3:43 pm

Re: Excel

Post by Square Rob »

Shirley wrote: Sun Jan 27, 2019 11:06 pm Yeah, it all depends on the goal of the chart. If each product needs to be tracked separately, then a grouped bar chart is probably best. If it's total revenue that's most important, but you still want to see the contribution of each product, then a stacked bar chart is better.
I’d do a combination chart, where the total revenue is represented by a line which is overlaid on a 100% stacked bar chart showing the % rev for each product line. That shows the overall revenue dynamic quite nicely.
User avatar
EnochRoot
The Dude
Posts: 6191
Joined: Tue Dec 01, 2015 6:18 pm

Re: Excel

Post by EnochRoot »

Thanks for the help guys. I’m going to start with the grouped bar chart and see where it goes from there.
Noli Timere Messorem
User avatar
brian
The Dude
Posts: 27740
Joined: Mon Mar 18, 2013 10:52 am
Location: Downtown Las Vegas

Re: Excel

Post by brian »

This is peak Harbaugh here.

Bandwagon fan of the 2023 STANLEY CUP CHAMPIONS!
User avatar
mister d
The Dude
Posts: 29047
Joined: Tue Mar 12, 2013 8:15 am

Re: Excel

Post by mister d »

Word sucks ass. Team Harbaugh.
Johnnie wrote: Sat Sep 10, 2022 8:13 pmOh shit, you just reminded me about toilet paper.
User avatar
wlu_lax6
The Dude
Posts: 10402
Joined: Tue Mar 12, 2013 7:16 am

Re: Excel

Post by wlu_lax6 »

brian wrote: Mon Aug 05, 2019 4:13 pm This is peak Harbaugh here.

As a person who worked for the Microsoft Office team....I am with the coach here.
User avatar
A_B
The Dude
Posts: 23323
Joined: Mon Mar 11, 2013 7:36 am
Location: Getting them boards like a wolf in the chicken pen.

Re: Excel

Post by A_B »

I love me some excel. But that’s a crime against humanity.
You know what you need? A lyrical sucker punch to the face.
Post Reply