Calculate averages

Hi there I'm a real newbie with Excel so excuse if questions are dumb.

I want to create a spreadsheet to calculate averages for sport.

It will be for cricket. The columns will be as follow:-

1. Amount of innings
2. Times not out
3. Total amount of runs
4. Highest score
5. Average

The average (column4) must be column 3 divided by (column 1 minus column 2)

So the average must be the total amount of runs scored divided by the amount of times out. (1 minus 2)

I would like to be able to just add scores after each game to calculate new average.

Hope I make sense.

Thanks

Originally Posted by MagicMan0027

Hi .....calculate averages for sport.

It will be for cricket. The columns will be as follow:-

1. Amount of innings
2. Times not out
3. Total amount of runs
4. Highest score
5. Average

The aver.............

So the average must be the total amount of runs scored divided by the amount of times out. (1 minus 2)

I w..........Thanks

Welcome to the Forum.

I'm taking that the highest score is already included in the Total amount of runs.

Example:

Cells:
A1 = Amount of innings
B1 = Times not out
C1 = Total amount of runs
D1 = Highest score
E1 = Where the formula will be placed.

Please Login or Register  to view this content.

Hope that helps


Cheers

ratcat

Originally Posted by ratcat

Welcome to the Forum.

I'm taking that the highest score is already included in the Total amount of runs.

Example:

Cells:
A1 = Amount of innings
B1 = Times not out
C1 = Total amount of runs
D1 = Highest score
E1 = Where the formula will be placed.

Please Login or Register  to view this content.

Hope that helps


Cheers

ratcat

Yes highest score already included in total.

Thank you I will try it.

I would like to be able to just enter each player's runs scored after every game and the average will be automatically adjusted. I don't know if it's possible. The list will contain about 20 players.

Thanks

Originally Posted by MagicMan0027

I would like to be able to just enter each player's runs scored after every game and the average will be automatically adjusted. I don't know if it's possible. The list will contain about 20 players.

Thanks

G'day MagicMan007,

It can be done. You are going to upload an example (a zip file) or explain the layout of the where the data is going to be entered.

How do you want to display the not out ?

Originally Posted by ratcat

Hello again,

Here an idea that I cooked up

Edit: If the player got out for a duck, just put a zero and if he/she did not bat leave the cell blank.

If the player was not out place an * in the not out column.

The totals (Match played, Not out, Total Runs, Highest Score, Average Runs) will automatic total has you enter the runs.

The best thing is play around with the spreadsheet. Enter your raw data that you have and have fun.

Cheers

ratcat

Sorry for the late reply. Thanks a lot ratcat it works great ! I'll just try and edit it to allow for more games and then I want to start with a spreadsheet for bowlers. Again thank you !!!

Originally Posted by MagicMan0027

Sorry for the late reply. Thanks a lot ratcat it works great ! I'll just try and edit it to allow for more games and then I want to start with a spreadsheet for bowlers. Again thank you !!!

Glad you like it.

If you get struck with anything just PM me.

Cheers

ratcat

Hi ratcat

It's me the newbie. Maybe you can help - I've been playing with a program for bowling stats but I'm battling with 1 or 2 things. When a complete over is bowled everything is fine but when I have something like 1.1 to 1.5 overs I get problems with my RPO and StrikeRate.

When I enter for example 1.2 in the overs column the totals balls at J6 are calculated as 7.2 balls instead of 8 balls. So from .1 - .5 of an over every ball is added as 0.6 instead of 1 ball. (A complete over being 6 balls)

How do I make excel see 0.1 as 1 ball and not 0.6 ball and 0.2 as 2 balls and not 1.2 balls; etc?

Then in the overs column (B) when adding up the total of overs how do I make 1.2ov + 1.5ov = 3.1ov and not 2.7ov ? So every .6 must become a full 1.

I don't know if I make sense or not ! Just ask if I'm unclear.

Thanks

MagicMan0027

cant edit above post????????? no option button!
zip was wrong try this instead