Page 4 of 5
Re: Excel
Posted: Tue Oct 16, 2018 4:02 pm
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.
Re: Excel
Posted: Tue Oct 16, 2018 4:03 pm
by mister d
The only real issue would be some new-to-the-list city being added after the summary/graph are created.
Re: Excel
Posted: Tue Oct 16, 2018 4:06 pm
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.
Re: Excel
Posted: Tue Oct 16, 2018 4:07 pm
by mister d
I can email an example later on if you need it.
Re: Excel
Posted: Tue Oct 16, 2018 4:19 pm
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.
Re: Excel
Posted: Tue Oct 16, 2018 6:49 pm
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.
Re: Excel
Posted: Tue Oct 16, 2018 6:50 pm
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.
Re: Excel
Posted: Tue Oct 16, 2018 7:55 pm
by mister d
If the list doesn’t look pretty you didn’t make a pretty list.
Re: Excel
Posted: Tue Oct 16, 2018 11:01 pm
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.
Re: Excel
Posted: Wed Oct 17, 2018 4:59 am
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.
Re: Excel
Posted: Wed Oct 17, 2018 5:18 am
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.
Re: Excel
Posted: Wed Oct 17, 2018 6:20 am
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.
Re: Excel
Posted: Fri Dec 14, 2018 1:52 pm
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?
Re: Excel
Posted: Fri Dec 14, 2018 2:06 pm
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?
Re: Excel
Posted: Fri Dec 14, 2018 2:07 pm
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.
Re: Excel
Posted: Fri Dec 14, 2018 2:11 pm
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.
Re: Excel
Posted: Fri Dec 14, 2018 2:19 pm
by EnochRoot
Re: Excel
Posted: Fri Dec 14, 2018 3:25 pm
by degenerasian
That might work, i'm trying to find an easier way
Re: Excel
Posted: Fri Dec 14, 2018 4:02 pm
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.
Re: Excel
Posted: Fri Dec 14, 2018 6:26 pm
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.
Re: Excel
Posted: Sat Dec 15, 2018 11:41 am
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?
Re: Excel
Posted: Sat Dec 15, 2018 5:44 pm
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.
Re: Excel
Posted: Sat Dec 15, 2018 5:45 pm
by A_B
I was thinking a series of if statements but I got distracted and didn’t come back to it.
Re: Excel
Posted: Sat Dec 15, 2018 8:54 pm
by EnochRoot
Thanks guys. We just added a comma in the cell after the email address and that did the trick.
Re: Excel
Posted: Sat Dec 15, 2018 9:00 pm
by mister d
Next level excel is oversolving your problems to avoid any manual fixes at all.
Re: Excel
Posted: Sat Dec 15, 2018 10:14 pm
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.
Re: Excel
Posted: Sun Jan 27, 2019 5:11 pm
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?
Re: Excel
Posted: Sun Jan 27, 2019 5:15 pm
by teeteebee
A bar graph could work. With each qtr broken down into four colored bars. Each quarter shows the change by product.
Re: Excel
Posted: Sun Jan 27, 2019 5:17 pm
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?
Re: Excel
Posted: Sun Jan 27, 2019 7:04 pm
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.)
Re: Excel
Posted: Sun Jan 27, 2019 7:32 pm
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.
Re: Excel
Posted: Sun Jan 27, 2019 10:07 pm
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?
Re: Excel
Posted: Sun Jan 27, 2019 10:19 pm
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.
Re: Excel
Posted: Sun Jan 27, 2019 11:06 pm
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.
Re: Excel
Posted: Mon Jan 28, 2019 8:20 am
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.
Re: Excel
Posted: Mon Jan 28, 2019 11:48 am
by EnochRoot
Thanks for the help guys. I’m going to start with the grouped bar chart and see where it goes from there.
Re: Excel
Posted: Mon Aug 05, 2019 4:13 pm
by brian
This is peak Harbaugh here.
Re: Excel
Posted: Mon Aug 05, 2019 4:15 pm
by mister d
Word sucks ass. Team Harbaugh.
Re: Excel
Posted: Mon Aug 05, 2019 4:57 pm
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.
Re: Excel
Posted: Mon Aug 05, 2019 5:16 pm
by A_B
I love me some excel. But that’s a crime against humanity.