Advanced Excel help?

Bleek · 6 Oct 2010 at 18:27

I want to create a spreadsheet with drop down (preset) options, I want to be able to store this data raw on another sheet and use it to create stats based on the win to loss ratio of a week and a day over the year.

I wanted a 'front' page with an overview of each month win v loss percentage and then breaking down in to days of the month to show if there's a pattern for certain days being more likely to win or lose.

Does that make sense?

How do go about doing this?

Example below:

Bleek · 6 Oct 2010 at 20:09

Wow that's very kind of you! I'll get on to it.

I just use Office 2002 Pro.

Bleek · 6 Oct 2010 at 20:18

Tenzen said:
just use a validation list for the dropdown.

Data--> Validation, select custom then just enter the values you need

But where is the data held, I want each element of the drop down to have a value so I can add them up and work out stats for them?

Bleek · 6 Oct 2010 at 20:58

Robbie G said:
Like this:

If you want the source data on another sheet, you have to name a range and link to it with '=Range' in the source section of the validation.

Ok so say you had 10 drop-downs and they half had 'A' select, half had 'B' selected, where would these 10 selections be stored so you could do a calculation from them?

I don't want a drop-down that's just a label, it needs to populate/store the data on how many times that drop-down selection is made.

Bleek · 6 Oct 2010 at 22:35

...because I don't know how to produce raw data from the drop-downs. That's the issue.

Bleek · 7 Oct 2010 at 00:05

drak3 said:
Assuming all dropdowns are under the same column, then define a named range and then use =COUNTIF to count the number of times option A,B,C, ... appears. The =COUNTIF functions will be your "front page" cells counting the times each of the options appears.

Now I see.

The difficulty comes when you want count the win/losses on a given day i.e. all the Monday's in the year, what would be the function for that?

Bleek · 8 Oct 2010 at 18:48

Thanks for all the replies thus far guys, been a massive help.

Can excel be as clever as to change a cell from green to red when a number goes in to the negative?

Bleek · 13 Oct 2010 at 11:40

Guys you've been a great help and it's much appreciated.

However the next challenge might be too much to ask!

Is there anyway to create a winning and loosing streak i.e. a count of the most wins and losses in a row?