Post the formula here, but if you got $s in the formula then this will most likely be your error.

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!

That would be the formula. I just tried it with a sample and works fine for me. Maybe you have to use ";" instead of ","

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 !

Your link seems to be broken.

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.

you can just manually alter which cells it applies to in that edit window

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.

I just tried this formula in conditional formatting:
IF(A2>B2;1;0)

And it worked.

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

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.

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.

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.

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.

Thanks dave!

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?

How big is this? I'd honestly suggest using a SQL table for that, it will make your life so much easier.

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.

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.

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

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?

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?

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

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?

What are you doing specifically? And from where are you importing?

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?