Calculate averages

[MagicMan0027]

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

[ratcat]

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

[MagicMan0027]

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.

[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

[ratcat]

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 ?

[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

[MagicMan0027]

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 !!!

[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 !!!

Glad you like it.

If you get struck with anything just PM me.

Cheers

ratcat

[MagicMan0027]

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

[ratcat]

Gday MM0027,

Being enjoying the IPL matches.?

I'll look into it. Maybe a slow response.

Anyone else is welcome to solve the problem

Cheers

[martindwilson]

ok ive had a look too
now with a visit here
http://www.mrexcel.com/archive2/4000/4438.htm
i found this code (name modified to make it clearer what it does)
its a udf which adds a convert base function

Please Login or Register  to view this content.

now with this in a new module
you can use it to manipulate
20.4+30.5 =51.3
see attached put overs in col a and running total returned col b
formula in b2 down

[martindwilson]

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