Open Side Menu Go to the Top
Register
Ask me anything about Microsoft Excel Ask me anything about Microsoft Excel

09-11-2014 , 09:30 AM
Post the formula here, but if you got $s in the formula then this will most likely be your error.
Ask me anything about Microsoft Excel Quote
09-11-2014 , 11:21 AM
Quote:
Originally Posted by Spurious
=AVERAGEIFS(T5:T66,T5:T66,">"&0) (the &0 is optional I think)
Quote:
Originally Posted by ffr
I pasted that in, but it didn't work. It just entered the actual text into the cell and didn't actually calculate anything.
Sorry if there was a reply above. I wasn't sure if any of the answers above applied to my issue.

What would be the formula to use to calculate the average from a range between T5:T66...excluding all blank cells and all cells with 0?

Thanks!
Ask me anything about Microsoft Excel Quote
09-11-2014 , 11:31 AM
That would be the formula. I just tried it with a sample and works fine for me. Maybe you have to use ";" instead of ","
Ask me anything about Microsoft Excel Quote
09-11-2014 , 02:22 PM
Quote:
Originally Posted by CrazyEyez
I'll give this a shot, but my stats classes were a long time ago and I slept through most of them...

Home elo after game (column Z) is Ranew from the first link, Home elo before game (column U) is Raold. You first need to turn column Z into a formula based on the equations in the link. I tried doing it, but I didn't end up with the same values for column Z so I'm not sure what I did wrong. I guessed at some of the inputs since not all the columns are labeled. I assumed:

K = column Q
Sa = column S
Ra = column U
Rb = column V

If that's right, then the formula to calc Ea would be
=1/(1+POWER(10,(V2-U2)/400))
Entered in cell AB2. The formula for RaNew entered in cell AC2 would be:
=U2+(Q2*(S2-AB2))

In theory AC2 would equal Z2, but it doesn't. So either I've made a mistake creating the formulas or I'm not using the correct columns as inputs.

Lets say for the time being that those formulas were correct. Now comes a tricky bit. You'll notice that the elo before game is equal to the elo after game from that team's prior game. For example, DC United's elo before game in cell U2 is equal to DC's elo after game in cell Z3. You'd need to set up formulas for the 'before game' numbers so that they are pulled from the 'prior game' numbers. Then, since every game's prediction is a function (in part) of the prior game, when you change the K value EVERY game's elo will update. It's going to be cumbersome to set this up since you have to look for the team's prior game, yet the team could be listed in either the away column or home column. Off the top of my head I'd start by creating a key column which is a concatenation of date and team. If I spend the time to figure this part out I'll let you know.

Assuming you got this far, the next step is to have all the cells in column Q be references to a single other cell, so that you can change that one cell and have all the cells in Q update to match.

Then you add in formulas for least squares.
"Least squares means that the overall solution minimizes the sum of the squares of the errors made in the results of every single equation."
In our case, the 'error' is predicted outcome (Ea) versus actual outcome (column S; either 0, 0.5, or 1). This part I'm not sure of: how exactly is the error measured? Let's say it's just the difference between the two. Add a column with a formula to take the square of the difference. Now somewhere else on the sheet add a formula to SUM this new column of squares. This is our error aka sum of squares (I think), and we're trying to minimize it by changing the value of K. Now we can use Excel Solver.

So we still have some uncertainty in the equations and the correct definition of 'error', and some work to do with setting up all the formulas, but I think that's the gist of it. Sounds fun!
Here's the file again with most of the columns labeled and all the formulas. I didn't realize you needed the formulas to run the least squares calculation.

https://www.sendspace.com/file/ithy3d

For cell Q, do you mean that in cell Q2 and and all the ones below that should be something like =Q1 with Q1 being 70 (or some other number) ?

Thanks for your help so far. I didn't think it would be so much work !

Quote:
Originally Posted by CrazyEyez
This is bananas but:

https://dl.dropboxusercontent.com/u/...elosample.xlsx

Assuming all those formulas are good (big assumption) then you open the Excel Solver and

Set Objective: $AC$2
To: Min
By Changing Variable Cells: $AC$1
Your link seems to be broken.
Ask me anything about Microsoft Excel Quote
09-11-2014 , 05:01 PM
Quote:
Originally Posted by Spurious
Post the formula here, but if you got $s in the formula then this will most likely be your error.
Using the standard Conditional Formatting drop-down, selecting "greater than", and clicking the appropriate cell gives me a rule that reads like this when you edit it:

Format only cells with:
Cell Value || greater than || =$B$2

This is for A2. When I drag it out or format paint it I get all of the A's referrencing $B$2. If I take out the $'s then drag it out, it does the same thing.
Ask me anything about Microsoft Excel Quote
09-11-2014 , 06:15 PM
you can just manually alter which cells it applies to in that edit window
Ask me anything about Microsoft Excel Quote
09-11-2014 , 07:27 PM
Quote:
Originally Posted by HipsterDufes
Here's the file again with most of the columns labeled and all the formulas. I didn't realize you needed the formulas to run the least squares calculation.

https://www.sendspace.com/file/ithy3d
Perfect. Yeah you need the formulas because the Solver is going to change the value in Q, let everything re-calc, and sum up the error. It will keep doing this until it finds the value for column Q that gives you lowest error.
Quote:
For cell Q, do you mean that in cell Q2 and and all the ones below that should be something like =Q1 with Q1 being 70 (or some other number) ?
Exactly. Leave 70 in Q2. In Q3 put '=$Q$2' and copy down.
Then in AB2 put

=POWER(S2-W2,2)

This is the square of the error, with the error being the actual outcome (S2) minus the predicted outcome (W2). Now copy that formula down. Then in AC2 put a formula for the sum of column AB (or wherever you put the square of error forumula).

Now use the solver to find the minimum value or AC2 by changing the value in Q2. When I do it I get 16.09. Again, I'm not entirely sure that's the right formula to use for the error, but I think it is.
Ask me anything about Microsoft Excel Quote
09-12-2014 , 07:26 AM
Quote:
Originally Posted by Jihad2
Using the standard Conditional Formatting drop-down, selecting "greater than", and clicking the appropriate cell gives me a rule that reads like this when you edit it:

Format only cells with:
Cell Value || greater than || =$B$2

This is for A2. When I drag it out or format paint it I get all of the A's referrencing $B$2. If I take out the $'s then drag it out, it does the same thing.
I just tried this formula in conditional formatting:
IF(A2>B2;1;0)

And it worked.
Ask me anything about Microsoft Excel Quote
09-12-2014 , 03:51 PM
Quote:
Originally Posted by CrazyEyez
Perfect. Yeah you need the formulas because the Solver is going to change the value in Q, let everything re-calc, and sum up the error. It will keep doing this until it finds the value for column Q that gives you lowest error.


Exactly. Leave 70 in Q2. In Q3 put '=$Q$2' and copy down.
Then in AB2 put

=POWER(S2-W2,2)

This is the square of the error, with the error being the actual outcome (S2) minus the predicted outcome (W2). Now copy that formula down. Then in AC2 put a formula for the sum of column AB (or wherever you put the square of error forumula).

Now use the solver to find the minimum value or AC2 by changing the value in Q2. When I do it I get 16.09. Again, I'm not entirely sure that's the right formula to use for the error, but I think it is.
Awesome. Just went through all the steps myself and got the same number.
I'm surprised it's so low but the guys in those links came up with similar numbers so it's probly right.

http://clubelo.com/Articles/Inter-Le...justments.html

Quote:
Every time a club exchanges Elo points with a club from another league, on top of the exchanged points from the clubs, the leagues will exchange points as well. A certain amount of points will be added to the league's clubs, uniformly distributed. To find out how many points are ideal, I tested what factor is required to provide the best predictability for European Cup games.
Any idea how to go about finding the correct number here?
Ask me anything about Microsoft Excel Quote
09-12-2014 , 09:54 PM
Quote:
Originally Posted by HipsterDufes

http://clubelo.com/Articles/Inter-Le...justments.html



Any idea how to go about finding the correct number here?
Sounds tricky. You have the same scenario as before, but in addition if the teams are in different leagues you have to add elo points to every team in the winning league and subtract the same points from every team in the losing league. So you'd need to know which league each team is in. You'd need a new column set up like column Q for the 'bonus' point amount. Then alter the elo ending formula to add/subtract that new value if they are in different leagues. The hard part is getting it to add that value to all other teams in the same league while getting the chronology correct. IOW a team from league A wins on June 10. Starting elo for all teams in league A get the adjustment added in for their first game following June 10. Then you'd use the solver the same way as before but the variable is this new value.
Daunting.
Ask me anything about Microsoft Excel Quote
09-15-2014 , 03:04 AM
Quote:
Originally Posted by Phresh
Do you guys do any Excel-specific work? Like, I guess accountants or whatever? What type of jobs are out there which hinge on being super good at Excel? I'm downloading Excel tutorials and want to be an Excel thug.
We use Excel at my job for a few things.
- Creating and managing product listings.
- Creating extra programs because the programs we use suck. All out-of-the-box programs suck, so we sort of export everything we can and create tools to compensate.
- Allowing people to download SQL views into Excel so they can see what is important to them. The cool part of this is that we can run equations and VBA over the exported data and have it dynamically compute results.

We do give an Excel test to most new employees. Only one ever person passed it. I didn't take it myself.

Marketers and data analysts use Excel quite a bit as well.

Quote:
Originally Posted by d10
True, just saying if you want to use Excel as your user interface but you don't think VBA is powerful enough for what you want to do, you're probably wrong.
VBA is more powerful than most people would think for certain. The issue is that Excel workbooks have a low upper-bound of data it can handle, which can cause myriad speed and data integrity issues. Once you save a macro-enabled book with too many values, you can lose a ton of data from corrupted books, if you can open the book ever again that is. I used to create books that were 5000 x 220, which is... precarious and pretty stupid.

I think that the amount of time it takes to learn VBA and get stuff up and running on Excel as if it was a program is much better spent learning and using more robust tools. I know that this is dependent on the employer, but for your own stuff, I would save learning VBA for the day you really need it.

Also, at least in my experience, the way I use Excel at one job is dramatically different than how I use it at other jobs. When I was working in marketing, I used tons of charts and mathy tools. Now I do a ton of string processing. I'm sure my next job will require pivot tables or something else I haven't needed much before.
Ask me anything about Microsoft Excel Quote
09-15-2014 , 06:05 AM
I'm doing a little project where I'm creating graphs for equities of ranges, where the equity of each combo in the range is plotted on the graph. My problem comes where I try to graph different ranges on the same graph, because different ranges have different numbers of combos. In the following example I'm trying to graph how equities change from the flop to a couple of different turns....



The problem here is that the flop range is way wider in terms of combos than the turn ranges, hence the way the flop line extends way past the others. This makes it very tough to compare the lines.

What I'd like to do is instead of plotting each individual combo, I'd like to work out how much of a percentage of the entire range each combo is, and then have the x-axis as percentage of range rather than combo.
Ask me anything about Microsoft Excel Quote
09-15-2014 , 03:12 PM
Thanks dave!
Ask me anything about Microsoft Excel Quote
09-15-2014 , 05:45 PM
I'm working on making a contact manager for all the vendors I use at work. I have separated the vendors into different tabs labeled valves, pumps, vessels, hydraulics...ect. What I would like to do is search the entire workbook for a particular person or company and have their contact info pop up. Or search "check valves", and have all the valve vendors that have "check valves" pop up. Suggestions?
Ask me anything about Microsoft Excel Quote
09-15-2014 , 06:00 PM
How big is this? I'd honestly suggest using a SQL table for that, it will make your life so much easier.
Ask me anything about Microsoft Excel Quote
09-15-2014 , 06:13 PM
Or simply combine them all on one sheet with the category as a field and use autofilter.
Ask me anything about Microsoft Excel Quote
09-15-2014 , 07:04 PM
Quote:
Originally Posted by CrazyEyez
Or simply combine them all on one sheet with the category as a field and use autofilter.
Yeah this would essentially be a SQL table in Excel, but depending on the number of records this solution could get inefficient soon.

I do agree though that this is a viable solution.
Ask me anything about Microsoft Excel Quote
09-16-2014 , 11:50 AM
Quote:
Originally Posted by CrazyEyez
Sounds tricky. You have the same scenario as before, but in addition if the teams are in different leagues you have to add elo points to every team in the winning league and subtract the same points from every team in the losing league. So you'd need to know which league each team is in. You'd need a new column set up like column Q for the 'bonus' point amount. Then alter the elo ending formula to add/subtract that new value if they are in different leagues. The hard part is getting it to add that value to all other teams in the same league while getting the chronology correct. IOW a team from league A wins on June 10. Starting elo for all teams in league A get the adjustment added in for their first game following June 10. Then you'd use the solver the same way as before but the variable is this new value.
Daunting.
Daunting indeed. I'm thinking maybe I should hire a programmer to do all this for me because I seem to have a math/excel question about every article on that site. I'm bound to screw something up, haha.
Ask me anything about Microsoft Excel Quote
09-18-2014 , 07:12 PM
Searched the thread and didn't find any mention of power query, you guys definitely need to look into this. Imagine pulling in data from various sources (other spreadsheets, databases, scraping tabular data off websites, other queries published to organizational data catalog) and applying a set of steps to this data source to transform it into what you need. Then imagine being able to refresh this data and reapply the steps all with a single operation.

Then using power view to create interactive reports you can drill into that's powered by these queries. Power map can be used to plot geographical data. All from excel.

If your organization is using Office 365 then you can add Power BI on top of your published workbooks in a sharepoint site and share your reports and set them to auto refresh. You also get natural language queries (search engine style queries) and the ability to tune your model with synonyms and phrasings to improve query results.

Power query intro:
https://www.youtube.com/watch?v=T97FQTIP6LA

Power query is currently an add on you have to download for Excel

Long but really awesome demo: http://channel9.msdn.com/Events/Tech.../2014/DBI-B324

Last edited by slong; 09-18-2014 at 07:19 PM.
Ask me anything about Microsoft Excel Quote
09-24-2014 , 09:18 PM
so I have 3 documents that contain about 17,000 values total.

I threw them in excel into 3 columns for ease.

I need to find if there are any duplicates. I thought this would be really easy but I can't figure it out for some reason. I did the conditioning formatting, but for some reason it lit up like a Christmas tree.

Each value is like 16 numbers long, not sure if that matters or not....

xxxxxxx12345692
xxxxxxx12536585

the first few digtis are the same, not sure if that matters and I'm not sure why so many lit up when I did the conditioning formatting.

Does it matter that they're in 3 rows?
Ask me anything about Microsoft Excel Quote
09-24-2014 , 11:30 PM
Do you need them in separate columns? can you put them all in one column and then use the Remove Duplicates option in the Data menu?
Ask me anything about Microsoft Excel Quote
09-24-2014 , 11:45 PM
They can be in the same column, I just pasted them in 3 at first. I will try that out tomorrow.

Sent from my ADR6425LVW using 2+2 Forums
Ask me anything about Microsoft Excel Quote
09-26-2014 , 12:25 PM
I was importing live currency rates into a page but it really lags as it imports some graphics as well, is there any way I can do it so it just scrapes the text into the excel doc?
Ask me anything about Microsoft Excel Quote
09-26-2014 , 12:36 PM
What are you doing specifically? And from where are you importing?
Ask me anything about Microsoft Excel Quote
09-27-2014 , 03:40 PM
I have a poker spreadsheet which i input site balances and then it adds them up and converts it to £, since some sites are $/€/£. I was importing Yahoos market rates as for some reason at the time I couldn't get reuters to work. I was using the data tab in excel "from web" and then going to the webpage and highlighting the tables to import it, theres an option for when to refresh the feed as well etc. The problem is though it was lagging the spreadsheet a ton so i just starting manually importing the figures ocasionally. Is there a better way of going about this?
Ask me anything about Microsoft Excel Quote

      
m