Spreadsheet Help

[fobert]

Hi guys I am having a problem with my spreadsheet.

My spreadsheet is very simple. I get the two team playing for example TEAM A vs TEAM B. TEAM A has 100 point ranking while TEAM B has a 95 point ranking. I get the difference and compare it to the opening line. And from there I make my bet. All the information is on one spreadsheet.

I need a formula that would automatically look for the ranking of both teams on the spreadsheet and then find the difference and then compare it to the opening line. If there is a big difference I will play that team.

So basically I need a formula that looks up the ranking. So say DUKE vs ILLINOIS. The formula would automatically look for the values of the 2 teams in the spreadsheet, minus them, then compare em to the opening line.

THANKS

[DrunkenLullaby]

I would guess that you'll want to use something like the following:

=IF((VLOOKUP(team #1's name)-VLOOKUP(team #2's name))>threshold,"bet", "no bet"))

[fobert]

I need to know 2 formulas.


How to make for example Column A which are the teams and Column B which is the ranking for each team equal. So when Duke is A1 and I use A1 in my formula it would use the numerical value and not the text.

The 2nd is how do I get my spreadsheet to lookup the value for a team.

[shady610]

dont use spreadsheets. IMO its better to write down the game. Compare the teams. Pick what you think the line shoulf be and then compare it to the actual line. This is how I do the college Bball game.

[jon13009]

I am not really clear what you want, and hope this helps.
I think VLOOKUP is the formula you want to use here as noted above, and lullaby's example should work. I compare team values all the time using text names for the team as the reference using excel's VLOOKUP formula.

In a more drawn out example:to lookup the power ranking of a team name in cel B443, I would use the following formula (the cel numbers used are just for demonstration purposes):

=VLOOKUP(B443,A1:B15,2)

B443 is the cel where the team's name is in text form (Duke) you want to lookup.
A1:B15 is the range of the cel's where your data is (the team name you are looking up should be in the first column in identical text form (Duke, North Carolina......).
2 is the column number of the data (eg rankings) you want to use in the reference data range.

This formula should give you a value (ranking) in the cel you placed it in. Once you have those values for each team in their respective cels, you can subtract and compare them.

Try it, but if it doesn't work then there may be something wrong in the way you are formatting your data or have text names that do not match in the respective cels (spaces, capital letters....).

With all the bashing on the use of spreadsheets around here, I agree a spreadsheet is nothing more than a glorified calculator, but, for those of us not compiler inclined, spreadsheets can serve a purpose as an easy and quick to boot number cruncher particularly when comparing all the readily available NFL statistical team data available out there and cutting and pasting selective team data into a spreadsheet. VLOOKUP is a nice formula in excel to lookup, reference and compare the numerical data pasted from the internet in your spreadsheet, if properly used IMO, otherwise doing it by hand is just as good (but more time intensive) - or - if you prefer: just fade the public and take the home dog.

GL

[fobert]

ok thank you for your post... but it is still not working
I will try to clarify my problem

I have 2 sets of data that is imported from the web

Column A is the ranking of the teams. 1-336 teams.
Column B is the list of my teams(text)
Column C is there ranking value(numerical)
Column D is blank
Column E is the team that is favoured(text)
Column F is the spread(numerical)
Column G is the underdog(text)

Now what I need is for this formula to look up both the favourite, underdog, and to return the value for it from column C.

So for example Duke vs BostonCollege.
Duke would be listed in Column E as a fav.
Boston College would be listed in Column G as a underdog.

I need a formula to look for the corresponding ranking value by analyzing COLUMNS A-C.

[jon13009]

Fobert,

First of all, the ranking data has to be sorted alphabetically for VLOOKUP to work:

You have to highlight the first 3 columns (A,B,C) and sort the data alphabetically based on row B in ascending order (data, sort, no header row, sort by row B, ascending order...) In fact, it is a bit of an assumed habit to sort the NFL data alphabetically by team name in ascending order after cutting and pasting the data into a spreadsheet. Typically I use the NFL data and VLOOKUP to compare team ranking numbers.....

Then you can use VLOOKUP to retrieve the ranking data based on team names from columns E and G and subtract the values obtained by VLOOKUP....

See the attachment (examplerank.xls)

I only used 10 teams, made up numbers for the teams, sorted the ranking data (cols A-C) based on column B alphabetically in ascending order, looked up the ranking values for the teams in columns E and G using VLOOKUP and placed them in columns I and J and subtracted the ranking values in column K. (the numbers I am using really don't make sense because I had to do this in a short bit of time...)

I hope this is closer to what you are looking for.....

[jon13009]

Sorry, these numbers make more sense....

examplerankb.xls

[fobert]

thanks it works

only problem now is the imported power rankings doesnt match the imported odds... do u have a fix for this?

reason for my spreadsheet is that it saves me times..

[jon13009]

Sorry,

No, no clear fix for that......
You just have to be careful and format and import your data properly into Excel so you can use it.

GL