Advanced Excel help?

Soldato
Joined
7 May 2004
Posts
5,503
Location
Naked and afraid
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:

25h1hxu.jpg
 
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?
 
Like this:

sdfdsfsdf.jpg


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.
 
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?
 
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?
 
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?
 
Back
Top Bottom