Horizontal Bar Chart - Dynamic?

Hello All.
I am trying to create a horizontal bar chart that plots frequency.
Basically I have data in cells (From J12-J511) that returns a value of either "WIN" or "LOSS"
As a note, for example this data is derived from Column G where cell J12 formula would be =IF(D12<$D$7,"WIN","LOSS")
(Just thought I'd mention this)

I would love to create a horizontal bar chart that plots all occurances of "WIN" "LOSS" & their frequency.
Thing is, if I press F9,, the results change, so the data needs to be "dynamic?" (Not sure if this is the correct term to use).

So,, the chart would show all 2 colour bars, Blue for "WIN", red for "LOSS"
So in order the chart would 1st show all 1 occurrence, then 2, then 3, then 4 occurrences etc etc
So for example, if there were "WIN" occurred 4 times in a row out of the 500 trade sample & "LOSS" occurred 6 times, the horizontal bar for "WIN" would be blue and be 4, the horizontal "LOSS" red bar would come out to 6

I hope the above makes sense.
My experience in excel is very ltd.
I have tried looking at google video, some videos with charts, some seem very complicated and I am confused to be honest.
My spreadsheet is a work in progress that hopefully I can finish and upload to a blog I started.
I attach the sheet, and a screen capture that might help.

PS, some of the "Statistics" in the upper frame (IE Actual Losing %) aren't working,, as mentioned a work in progress, hopefully these can be functional at a later date, when I can work out how

I really hope someone can help me with this.
Many thanks
TheGhost

Hi

I hope the above makes sense.

Not much, I'm afraid. To me, anyway.

I would love to create a horizontal bar chart that plots all occurances of "WIN" "LOSS" & their frequency.

How do you determine the frequency? you have 500 cells, some have WIN, some have LOSS.

So,, the chart would show all 2 colour bars, Blue for "WIN", red for "LOSS"
So in order the chart would 1st show all 1 occurrence, then 2, then 3, then 4 occurrences etc etc

"1 occurrence"??? What do you mean?

So for example, if there were "WIN" occurred 4 times in a row out of the 500 trade sample & "LOSS" occurred 6 times, the horizontal bar for "WIN" would be blue and be 4, the horizontal "LOSS" red bar would come out to 6

I don't get it.

In any case, Excel won't be able to produce a chart from this data. You will need to create a table where you prepare the data for plotting. Excel charts do not summarize, total, or calculate, they can just plot numbers in cells.

Please try to explain what exactly you want the chart to show, and please keep in mind that we don't know your project as well as you do, so may require explanations for things that seem obvious to you.

Oh, and you may want to indicate in your profile that you're using 2007....

cheers

Hi Ghost,

see what you can do with the attached.

I have mocked up a sheet with 500 trades and I'm using a calculation based on a random number to fill the Status column with either Win or Loss. This gives me a nice data sample, that changes whenever I hit Enter.

Next to the Status column I count the number of repeats for each Win or Loss until the status changes

In the next column I determine whether a number is the last in a series of counts, e.g. is the "3" followed by a "4" or is the "3" followed by a "1". The three possible positions are

new = sits next to a count of 1 followed by a bigger number
middle = sits next to a number in the middle of a series
last = sits next to the highest number of the series, i.e. for a single Win/Loss it will be next to a 1, for a series of 5 consecutive Wins it will be next to a 5

With me so far?

Now to the juicy bit.

I now need to count how often the last number of a series is a Win and has a certain number of repeats. I use the SUMPRODUCT function for that. I have a grid with the number of repeats in the first column, and the words Win and Loss at the top. The SUMPRODUCT function in cell H17 calculates the number of sigle wins. It looks like this:

=SUMPRODUCT(--(status=H$3),--(position="last"),--(count=$G17))

it will look in the status column for the Wins only
it will look in the Position column for the "last" only
it will look in the count column for values that are equal to G17 (which is the 1)

The function will sum up those occurrences where all three conditions are found true.

This table is the basis for the bar chart, data labels at the inside of the bar, etc.

the next challenge is to have the percentage displayed in the chart, too.

First we need to calculate the percentage for each repeat. As you mentioned, we need to multiply the count values in the table by the repeat number, so the formula is

wins*repeats/500 - in row 17 this looks like =H17*$G17/500

This is formatted as percentage. Now we also see all the zero values, but we don't want them to show in the chart, so we better get rid of the zeros by wrapping th percentage calculation in an IF statement

=IF(H17=0,"",H17*$G17/500)

Great. Now we have only percentages showing when the numbers are greater than 0

To place the percentage labels in the chart, we need to add two data series to the chart, one for win and one for loss, and then change the chart type of these two series to be XY (Scatter). The X values for the two series are the same as for the bar chart, but the Y values need a bit of fine tuning. Since the XY chart series are plotted on a secondary Y axis, we can play with the Y values until we have the position just right.

Next we need a tool that does not come with Excel, but is available for free at www.appspro.com - the XY chart Labeler. Download the add-in and install it.

Then we use this tool to assing the calculated % values as labels to the XY series. Finally, we format the XY series to show no markers or lines, hide the secondary Y axis and the chart should look like in the attached file.

Hope that helps

Hi Ghost,

hope you had a good sleep

I've attached a version of your work sheet with the chart in place.

Since I'm working with XL2003 on this computer, there may be some stuff that got lost in translation, so please have a good look. I also did not bother with fancy bar formatting. You'll take care of that, ok?

I downloaded your spreadsheet, but the chart & repeat/win/loss columns wouldn't refresh like the "Status", "Count","Position" columns

I've created the range names "status", "position" and "count" for the columns J, P and O. These range names are referred to in the summary table of win and loss and if the names aren't defined, the formulas bomb.

Can't get I6 cell to work ,,,

Don't know what you want to achieve there, but I6 contains a circular reference. It says:
If there is data in D5:D9, then divide myself by (myself + AA4)

If an element of a formula refers to the cell where the formula is stored, the calculation would go in circles - forever. Some times there are valid reasons for circular references, and there are ways to allow them. But most times a circular reference is entered by mistake, you really meant to refer to a different cell. So, once you've caught up with your sleep, try and see if you really mean
=IF(COUNTA($D$5:$D$9),I6/(I6+AA4),"")
(the red bits are the problem!)

By the way, the chart in my first example had a white background, so the white data labels for the win/loss bars were effectively invisible when the value of the bar was zero.

Now, the chart has a dark gray background, but the data labels are still white. In order to prevent them from showing for all the zero values, I used a formatting trick that you may or may not know.

Custom format data labels to
0;;;

The logic behind this is the syntax of the custom format

<positive number>;<negative number>;<zero value>;<text>

If you only specify one of the four options, it will be the default for the other options as well, but if you put the semicolons in the custom format you thus define the format for each of the options, in this case

positive number = 0 (general)
negative number = (nothing, will not show)
zero value = (nothing, will not show)
etc

hope this puts you a step further to your goal

cheers

Hi,

I fixed the chart so it now shows the percentage labels. Hope you can see it.
Also, the chart was not pointing to the win/loss data. I fixed that, too.

Whenever I hit F9, the numbers update and the chart changes.

I noticed that you have entered the sumproduct formulae in the win/loss table with CTRL-SHIFT-ENTER. There is really no need to enter them as array formulae. I'm not even sure if that is a good thing, but I don't know much about them.

Apart from the chart, I can't help you much further, I'm afraid, because I don't really understand what the spreadsheet does.

But you seem to have a good grip on it now.

cheers

Hi,

this is really a troublesome little bugger!

I think I found what was not working and caused the error messages. Try the attached now.

I have saved is as a XL 2003 *.xls file, so some of the finer points of XL 2007 formatting and functions may have been lost, but you should see the chart working and changing every time you hit F9.

Of course, you can save this now as a XL2007 file and continue from there.

hope that helps

Tell you what: It's 5pm here and I'm going home. In the office I have only xl 2003, but at home I have 2007. I'll open one of the versions above in 2007 and try to get to the bottom of it. If it works for me in 2007, I'll post it back. That should take care of the compatibility issues.

Hang in there. We'll get it done!

cheers

Jeeps, this one has me stumped. But you can stop tearing your hair out.

For some reason, Excel 2007 does not update a formula like

=SUMPRODUCT(--(Outcome=$O$11),--(position="last"),--(count=M12))

Not when you hit F9, anyway, only when you edit the formula and hit enter.

But it does calculate with the syntax

=SUMPRODUCT((Outcome=$O$11)*(position="last")*(count=M12))

Also, since Excel 2007 has the new sumifs and countifs formulae, I could use

=COUNTIFS(Outcome,$N$11,position,"last",count,M12)

I used the Countifs version in the WIN column and the SUMPRODUCT in the Loss column, so you can enjoy both.

The links are cleared, the chart updates, --- as far as I'm concerned, this is now working fine.

I'll never touch this file with XL 2003 again, promise!

Hope that solves your immediate problem. Cheers! Sleep well!