Excel Newbie hopefully simple question 3

[Ray1127]

I have an Excel Spreadsheet with 4 Columns for a bowling league. The spreadsheet tracks the Bowlers By Average Ranking them 1st to last.

Heres the structure

Name Average Rank Pins behind
Tom 178 1 -
Sam 173 2 4
Bill 173 2 4
Sally 170 3 8

Name Never changes.
Average changes each week
Pins behind is calculated with the following formula from Cell B3
Where B3 = the current row 3 and changes for each row below

=IF(MAX(B2:B24)=B3,"-",MAX(B2:B24)-B3)

What I'd like to do First is create a formula to get the rank rather than have to manually change it each week. Second in the Fall we will have three times as many bowlers in the league and I'd like to create a VBA Formula so that the
=IF(MAX(B2:B24)=B3,"-",MAX(B2:B24)-B3) Formula doesn't have to be changed each time I copy it. As it is if I copy the formula from 1 cell to the next it automatically adjusts the cells which is not what I want.

 

[SkipVought]

hi,

have to manually change it each week

]quote]Formula doesn't have to be changed each time I copy it.[/quote]
Seems like you're pulling your car, because you don't have a horse.

Excel is an extremely versatile tool.

If you were to enter each week's bowling scores in a properly structured table, you could have your summary statistics calculated and sorted in seconds.

You resll do not need VBA to summarize bowling scores automatically.

Start with a Score Table
[tt]
Date Name Score
[/tt]
From there, your summary can be generated, using Excel's Statistical functions like COUNTIF or AVERAGEIF or even using the PivotTable feature.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

Thanks skip I'll try that. As I stated I'm not real familiar with Excel.

 

[SkipVought]

Build your table.

Post back with a sample of your table data and any specific summarization question and the results you expect. That way, anyone that answers, has some specific data to work with and relate to you.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

Here's what I'm trying to get to.
Pins
Bowler Avg Behind Rank
Tom 174 - 1
Ray 173 1 2
Brian 166 8 3
Gary 166 8 4
Hershel 165 9 5
Andre 162 4 6
Chuck 162 12 7
Dave 158 16 8
Henry 148 26 9
Karen 143 30 10
Warren 141 33 11
Mary 133 41 12
Sean 131 43 13
Mike 130 44 14
Carol 128 46 15
Josh 127 47 16
Nena 127 47 17
Angel 124 49 18
Lam 120 54 19
Vickie 96 78 20
Doug 91 83 21
Carol 78 96 22


Pins Behind is calculated using the formula in my first post. The rank is simply typed in each week after sorting on Average

I created the Pivot table but in the Pins Behind and the Rank I get #N/A

I inserted the Avg column 3 times in the Values section of the Pivot table. The Avg works ok. The Other 2 in the Summarize Values By tab when editing the Value I use the Sum Function The Show values as tab I have show values as difference from (Pins Behind) Base Field = Avg. Base Item = 174

(Rank) show Values As = Rank from largest to Smallest
Base field = Avg.

 

[Ray1127]

Looked better when I was typing it. but you get the idea.

 

[SkipVought]

Where is your SOURCE TABLE, the one that has each weeks scores for each bowler?

THAT is what we need to start with!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...continuing.

Aveage, Rank and Pins Behind are ALL CALCUALTED.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ray1127]

Don't have that the spreadsheet was given to me by the league secretary when she found out I worked in IT. She asked me to take a look at it since it took her some time to enter the data. That is why the average is not calculated. The process is that the bowling center gives her a weekly report that has the standings. a season High scores section a weekly high scores section and each team including bowlers name, average total pins total games and last weeks scores. For this league there are awards for the league standings, Season Highs and Average High to low. Each week she types in the current average for each bowler and then sorts it on average high to low. She then types in the other 2 columns Pins Behind leader and Rank. So the data is overwritten each week. My goal is to set it up so that she can simply overwrite each average and the pins behind and Rank is automatically calculated. She has a basic version of office word, Excel and powerpoint that she uses. I could do this in a day in Access but she doesn't have Access and doesn't want it so the runtime version won't work either.

 

[SkipVought]

This is absolutely insane!

How can ANYONE calculate an average without the data points, although I suppose that one could keep track of the number of data points for the league and use that in the calculation???

Your league secretary could SIMPLY enter the scores for each week in a table and ALL THE STATISTECS would be AUTOMATICALLY CALCULATED!!! INSANE!!!
[tt]
rank: =RANK(B2,B:B)
behind: =MAX(B:B)-B2
[/tt]
assuming that your table headings start in ROW 1 Column A

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[mintjulep]

You should also read Excel's help regarding Relative and Absolute references

Switch between relative, absolute, and mixed references
Show AllHide All
1.Select the cell that contains the formula.
2.In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , select the reference you want to change.
3.Press F4 to toggle through the combinations. The "Changes To" column reflects how a reference type updates if a formula containing the reference is copied two cells down and two cells to the right.


Formula being copied
Reference (Description) Changes to
$A$1 (absolute (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.) column and absolute row) $A$1
A$1 (relative (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) column and absolute row) C$1
$A1 (absolute column and relative row) $A3
A1 (relative column and relative row) C3

Which looks better in the Excel help screen.

 

[Ray1127]

Skip I agree. It is insane No idea why she does it the way she does but the 2 formulas you gave me were what I needed. Those work. I'm going to try and set up something for the fall that she can use that will be much better suited. but this works for now.

 

[Ray1127]

MintJulep thanks I'm saving this thread for possible use in the fall I can see where I can use these for now I'll use Skips tips as they are easier and works for now.

 

[SkipVought]

Of course, the two formulas, once PASTED each of the row 2 cells, can be copied, and then pasted IN ONE OPERATION into the cells adjacent to the names, by the process
[tt]
1. COPY B2:C2
2. SELECT B2:Clast row
3. Edit > PASTE
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}