Tool Used - EXCEL 2016 , GOOGLE SHEET

What is the Average Age of the All the Players

-To do this first use year formula to find year from his given DOB i.e Year(Reference of DOB Cell )

Then subtract it with today's year.

For that use now() formula , put it in year(now) , to get todays year,

Then subtract that year with DOB year

What is the Average Score of all the Left-Handed Batsmen

-Using if formula in which condition given that if player is left handed then execute true , otherwise false.

To achieve this in condition give reference of cell in batting hand column = “left_hand”

If true , use sumif() to calculate player total score.

To achieve this

- select range as name of player in deliveries sheet of batsman column

- criterion is cell reference of name in players sheet

- sum range is total runs column in deliveries sheet.

Now average can be found by using 2 methods:-

1st:-

Find sum of all total score of all player using sum() function

Then count players for data created in column I ( Total score of Left-Handed Batsmen)

Divide total sum to no of player( count)

2nd:-

Use average formula to column I ( Total score of Left-Handed Batsmen)

What is the Maximum Score scored by a Bowler of Right Hand

This can be achieve just like above question but condition is changed

Now we have to find player with right hand in bowler column

For this wildcard is used

Wildcard in IF( ) doesn't work directly , we have use it with countif to generate 1 , 0 , which are true and false.

So here used COUNTIF(D2,"right*") , if this is true that only true condition will will other wise false

True condition is just like above question

Now using max () formula , find max score from column m ( Total score of Bowler of Right Hand ) of Player sheet

How many different teams are there

Simple apply unique () formula , in team 1 column of the matches sheet , this will give unique names of team.

Use it inside counta() Formula to count names and to know the answer

What is the Average Score of teams when they bat first

First need to find team who bat fist when win toss

For that use if statement , and give condition , if cell reference is bat , that give winning toss team name , otherwise another team

Now count no. of time team bat first ( individually for all teams)

Calculate total score of that team using deliveries sheet giving reference of team name using sumif() formula

Now average is divide total score with count team bat first.

No. of unique venues In IPL

Use filter() formula , select range , cell reference to name of season year

This will shows name of venue of that season year only

Now using in inside unique will give unique venues names

Now using this inside countA() will give no. of unique venues

Now made dashboard using all those data , giving reference of them , using some creativity , data bars and charts.

Skills/knowledge learned:-

- How to use Year formula , learn how it work

=(YEAR(NOW())-YEAR(B4)

-Using nested if with countif and sumIF

-wildacrd (*) with countif

-unique () function

-unique() inside count()formula

=COUNTA(unique(Matches!E2:E757))

-Sort() and unique() together

-count(),unique() , filter () together

Prateek Kumar Agarwal

Exploring my interest to play with data.

--

3