Hi All,

I know most of the Indians are very much cricket enthusiastic. Did you ever try to add the overs in Excel? Here is a way to calculate the overs in Cricket.


B C D
4 Overs
5 Z Khan 9.4 9.67
6 Ishant 8 8.00
7 Harbhajan 10 10.00
8 Yuvraj 4.2 4.33
9 Sachin 5.4 5.67
10 Munaf 10 10.00
11 Total Over 47.4

Spreadsheet Formulas
Cell Formula
D5 =DOLLARDE(C5,6)
D6 =DOLLARDE(C6,6)
D7 =DOLLARDE(C7,6)
D8 =DOLLARDE(C8,6)
D9 =DOLLARDE(C9,6)
D10 =DOLLARDE(C10,6)
D11 =DOLLARFR(SUM(D5: D10),6)

You must need to install Analysis Toolpak add-in via Tools > Add-Ins > check Analysis Toolpak.


Now see some of the formulas;

To find the last numeric in a range (A1:A100), the formulas would be

=LOOKUP(9.999999999999E+307,A1;A100)

Now 9.99999999999E+307 is the biggest number one can enter in Excel

So to find the last string,

=LOOKUP(REPT(“z”,255),A1:A100)

Now to find the last non-zero value,

=LOOKUP(2,1/(A1:A100<>0),A1:A100)

So to find the first non-zero value

=INDEX(A1:A100,MATCH(1,(A1:A100)<>0,0))

Array Entered.

I hope this would be handy to you all

BTW, I'm not allowing to post html code to represent the Excel sheet over here.