Horizontal Bar Chart - Dynamic?

**theghost** · 09-18-2009, 10:37 PM

Many thanks again for your help Teylyn,
it really is getting there. :-)

I have been on the sheet today about 8hrs,, got as far as I can go I think,
eyes are killing me :-)

I attach latest version,, so much I could type, but I'll try and keep it tight.

Managed to sort out most of the " Statistics" data in the top frame, that returns a lot of info.
I had trouble with 1 formula,(The actual Losing %), took me ages to sort out,,, all it was, was that I had a space before = in the formula bar

hehe,, also,,, some are array formulas? Not sure exactly what it means but now know I must use Ctrl/Shift/Enter

The clear button now clears all data correctly.
Managed to get rid of many hidden Rows of Data.
(Hidden Rows are now Between Column J & T)

I've really tried Teylyn to insert all your cells of data correctly.
Problems are;
I really didn't know how best to do the "Frequency" Chart.. So I copied and pasted it into this sheet.
Trouble is,,, I keep getting a security warning about links,, I really don't know how to get ride of it.

I did have a few issues also,, I attach screen shot,, not even sure what it all was about,, but just thought I'd mention it.

I see what you mean about "Naming the range of cells",, ie position/count (I called status, Outcome).
Count & Position Columns Refresh with F9,,
But I just Can't get the Win/Loss Columns To Update Teylyn? (Columns N & O)

As to The chart, well, I've done what I can, but if the Win/Loss doesn't update, so it won't work.
Can you advise on how to do this Teylyn?

I've also got trouble Formatting % on chart.
I read your 0;;; but still not displaying correctly
Only other thing (Apart for "Simulation" but that's a completely different kettle of fish) is I
would like to include in the stats area:
"Largest Losing Streak"(Cell I8)
&
"Largest Winning Streak" (Cell W8)
If Cells I7 & W7 can calculate the Longest Losing Streak & Longest Winning Streak, hopefully there might be a way to work out what that amount is?
If anyone can help with this I would be most grateful,, I really can't do this, it's beyond me.

I've really tried my best Teylyn.
Hope you can advise.

It's really getting close & looking great.
Worth the effort.

Again many thanks for your help...
& One other special mention,
I had some great help with the Stats formulas from a guy called Jon Von Der Heyden.
I just want to publicly thank him for his help in the writing of most of the Stats Formulas.
I couldn't have done this, so I am extremely thankful for his help & time, a very talented excel programmer, many thanks.

Many thanks for everyone's help.

Thank you for your time again Teylyn, it really is getting there.
The spreadsheet wouldn't be where it is now, &I couldn't have done this chart without your help.

Right, 3.30am, off to bed
Many Thanks again
TheGhost :-)

**teylyn** · 09-20-2009, 08:35 PM

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

**theghost** · 09-23-2009, 06:13 PM

Many thanks for your reply Teylyn.
Sorry for my late reply, had major electrical problems,, sorted now, needed new wall sockets!

Regarding your uploaded spreadsheet.
I just can't get it working Teylyn I'm afraid. I can't get the chart or all data to update.
I've attached a screen capture, showing various options I'm seeing regarding excel and options. (The link problem)

Is the chart some kind of saved template Teylyn? Where it's looking for info on your HD and so I it won't work properly when you send?
(That's just a guess, but best I can think of).

I've attached another sheet Teylyn, MCE7. There is no chart in it.
Is it possible you can just alter Column N(WIN Column) & Column O, as these 2 are meant to update? Is this right Teylyn?

It's just that in all the sheets I have ,, none off these columns update.
I just can't see why not.

If I can just get all the data working 1st, then I can look at the chart.
(The chart is another issue Teylyn as I did have problems formatting, but I won't go into that now, so as to keep this email short,, but if you could kindly just show me what is needed in Col N & O so these update correctly.
Column K (Count) & L (Position) are working fine.

I just can't see what I've done wrong/or what is missing.

I attach now
Again many thanks for your help with all this, it is very much appreciated.
Many Thanks
TheGhost