Advanced Excel help?

Bleek

Bleek

Bleek

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
 
if you mock it up (the sheets) with the formatting and what you want in each cell for example say you wanted in Cell G4 "Win Ratio for week"
Put in cell G4 "win Ratio from Results for the week"

Repeat for everything you want Done

ill have a look at it when i got some time, & see what i can do to help?

Ps. What excel/Openoffice version do you need it to work in?

Upload it to google docs or email me in trust & ill look at it when i have time, bare in mind i will moving house next week but i should be able to look at it way before then :)
 
Last edited:
just use a validation list for the dropdown.

Data--> Validation, select custom then just enter the values you need
 
Tenzen said:
just use a validation list for the dropdown.

Data--> Validation, select custom then just enter the values you need
Click to expand...

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.
 
Bleek said:
Wow that's very kind of you! I'll get on to it. :)

I just use Office 2002 Pro.
Click to expand...

im no expert, but have learnt a fair bit of Excel over the last few months with formulas & VB.

if it comes to it i have at least 2 mates who are Excel Experts to so :)
 
Robbie G said:
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.
Click to expand...

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 said:
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:
Click to expand...

To make it easier, why not have the drop down options on the same sheet as the one you want to store the raw data on?

You can then have your front page use a countif function to help work out the stats?
 
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.
 
Last edited:
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.
Click to expand...

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 said:
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.
Click to expand...

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

The drop downs ARE the raw data. If there's a drop down with 'A, B, C...' the raw data becomes whatever you selected from the drop down.

As drak3 says, use =COUNTIF to work out the number of wins, losses, or draws for a given period.

So:

=COUNTIF($A$1:$A$50$,"W") for wins, etc.
 
Bleek said:
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?
Click to expand...

Assuming column B is your days of the week and C is your W/D/L

=SUMPRODUCT(--(B1:B10="Monday"),--(C1:C10="W"))

If you wanted to be really smart, replace "Monday" and "W" with cell references to two cells you have created with drop down boxes; one for the day of the week and one for W/D/L.
 
IMHO I don't think you need to use COUNTIF's or SUMPRODUCTs formulas..

Heres what I would do:

1. Have 1 workbook and use two tabs
2. tab1 = raw data, tab2 = analysis
3. On tab1, I would knock up a quick table with the following headers, week, day and possibly month (if you use fiscal weeks you don't need months(just for easy viewing if anything)
4. on tab2 I would have another chart where the data is broken down into days, weeks months and years
5. On tab1 I would further set preset the options that users can choose from and make it so that they cannot enter any other data.

How it works:

User enters the day, week, and W/L/D on tab1, the entered data is then picked up in tab2 at the end of the day / week and month. Data is picked up in tab2 by cross referencing the cells from tab1, so in tab2 cells you enter the cell reference for tab1, week1, day1......

First concentrate on getting daily data then it will be easier to break it down into weeks and months.

I hope this helps, its purely based on my understanding from your first post..
 
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?
 
Can excel be as clever as to change a cell from green to red when a number goes in to the negative?
Click to expand...

yes if using 2007 and 2010 versions

you can also apply a filter on the cell header and filter "choose to see" on colours...
 
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?
 
You must log in or register to reply here.
Back
Top Bottom