Posts tagged Advanced

Question : Excel 2007 advanced formula construction help needed. Please read details.?
What follows is a detailed description of the spread that I construct every year and what purpose some of the cells serve. Keep in mind that I’m seeking a faster way to write all the formulas than I do now.

SCENARIO:

When the NBA, NFL, NHL, MLS, and MLB issue the new schedule each season, I’ll copy it to a spread so that I can keep track of how each team is performing for historical purposes. (I’ve encountered many errors from other data services so I prefer to keep my own.)

I arrange all of them in a similar pattern, but lets use the NBA as the example;

Column A = I’ll enumerate each row as it is labeled on the worksheet to the end of the schedule. This season takes up 1,237 rows including the monthly headers.
Column B = Date of game
Column C = Visiting team
Column D = Win (assigned a value of 1 if applicable)
Column E = Loss (assigned a value of 1 if applicable)
Column F = Points For
Column G = Points Against

The same pattern continues for the Home team on the same row beginning in Column H and continuing through to Column L except for the date.

Then in Column M, I’ll list each team alphabetically and the next four Column’s will have the same corresponding headers (Team-W-L-PF-PA) that are meant to indicate the running totals of each team.

I then have two more similar arrangements – beginning with Column R that break down the teams in Home and Away categories.

In constructing the formulas, I’ll do an alphabetical sort from Column A to Column G so that I can highlight one team (EX: I’ll make “Atlanta” in bold red) and then re-sort back to their original order to begin the formula process. Since Excel can handle up 40 some cells in a single formula, and each team plays 82 games, I break the season down by the month to get around that limitation.

That process has me going to Column R (next to “Atlanta”) and In R1, My formula goes =SUM(??,??,??) What gets filled in the argument are the coordinates of the Win Column next to all Atlanta away games, month-by-month. From there, I simply copy and paste the formula to the next three cells to sum the Losses, Points For, and Points Against.

QUESTION:

Is there a quicker method that I can use to construct these spreads much faster? Is there a way to sort say all the away teams, construct a formula for each team that tracks all their away game results, and then re-sort the away game list back to its original layout without the formulas getting mixed up?

I’m currently in an Excel 2007 Intermediate course and we just went over “Lists and Tables” and I thought there might be a way. I tried to ask the instructor for some advice, but I think my lack of experience stood in the way of effectively communicating to him exactly what I was after.

Apologies if the details are too long here but I wanted to be thorough. My e-mail link is open here so feel free to ask me any questions you might have.

Thanks, and I look forward to your answer.
atlanta new home construction

Best answer:

Answer by Cozmosis
Consider using the SUMIF function instead of the SUM function. SUMIF allows you to sum an entire range of cells if a criteria in another range of cells is met.

Example:
If your data is on Sheet1 and you want the total wins for away games (column D) for Atlanta
Make a list of all the teams on Sheet2 in column A (A1 is Atlanta)
Put this in Sheet2 cell B1
=SUMIF(Sheet1!$ C:$ C, $ A1, Sheet1!D:D)

This will sum all the values in column D on Sheet1 where column C on Sheet1 is Atlanta (Sheet2 cell A1). Copy the formula down column B on sheet2 for each team. You now have a column for total away game wins for each team.

Copy column B on sheet2 and paste it in column C and you now have a column of all the away game losses (Sheet1 column E) for each team.

Excel 2007 also has a SUMIFS function that does the same thing except for more than one criteria. There are also the COUNTIF and COUNTIFS functions that may be of help.

You may also want to consider making a Pivot Table. It’s too much to give you a step-by-step Pivot Table guide in this venue. They can be complicated at first to setup, but once they are, you have a lot of flexibility to organize your data. Do a search for “Excel Pivot Table Tutorials” and you’ll find several good sites.
.