MS Excel: Custom Data Labels

In Microsoft Excel, if you ever needed to graph some data but then attach a data label with it that wasn’t necessarily the data value of that point, there is a way to do that. This also a good way to attach notes to the graphs and can be easily customized.

Let us assume this is your data:

You want to plot the first half of that data like normal:

Next you can right click on each of three bars and select Format Data Series. Now select the Data Labels tab and select Values and press OK.

You should now see the graph like this:

Now since you want to associate the second half of the data values with this bar graph, you select each data value individually – you can do this by click the value once, to select all the values associated with that color:

Then click again to select that point individually:

Now press the “=” key and select the cell the new data value that you would like to show. Repeat this last process for each data point and you will be set to go:

I am pretty sure you can write up a VB script for it and I probably will in due time if necessary, but hopefully this helps for now.

Excel Help Request [Solved]

[Edit] Problem solved, using a SumIF ended up fixing both of my problems. Thanks to Mr. Feraudo. [/Edit]

For you Microsoft Excel experts out there, I have data on the scoring for an individual player and I am trying to figure out the use of a formula or two to calculate the following: I need to calculate the number of goals a person scored at Home (or Away) without individually counting them and then do the same thing for games in which the player’s team Won or Loss.

I thought about using CountIf but I’m not sure how to count items from a range of data based on a criteria that is not necessarily in the range. For example, if I want to count items from the Goals column based upon whether the Location is Home or not. Also, I’m not too familiar with using the Offset formula to calculate a column’s worth of data in a single cell as opposed to repeat it for every cell.

Any suggestions would be greatly appreciated. Even if you think you have something that might work but you aren’t too sure, feel free to shoot me an email or talk to me online about it.


Microsoft Excel Anyone?

So I have been going Excel crazy recently by trying to compile stats, keep score and just record wins and losses over several seasons for teams. So I’m going to focus this for anyone who is some what resourceful with Excel and wants to help me out.

My dilemma is that I have a column that is counting the streak of the said team. For example, let’s say if the team wins 5 in a row, loses 2 in a row and then wins one… the column will say the following:

W1, W2, W3, W4, W5, L1, L2, W1

Now that is a relatively small scope of what i have. I have recorded data of over 130 games so far, and I am trying to figure out a way to automatically calculate what is the longest winning streak, losing streak, etc.

I can provide the Excel sheet if someone wants to give it a try. Any help or suggestions of further stats to determine from the given data would also be greatly appreciated. Just leave your email in the comments or contact me directly and I can send you the file.