Dynamic Addition,MAX,MIN with "*" for Cricket from Different Tabs.

[all4excel]

Calculating Average,Max,Min across Different Sheets & Rows for Cricket with "*"...

Things looking to achieve already shown in Cricket-III file as well as somethings from Dynamic Addition-4.0 file..

BATTING :
Total Runs ---Total Runs including numbers with and without asterik ( * )

Not Out--Only counting the instances when the Batsman was "Not-out".

Average--Total Runs / ( Total Matches played - Not Out instances )

Highest--Highest score - there are 2 instances of 186 and 186*, then the one with [ * ] would be considered.

Lowest--Lowest score - if there are two instances of 48 and 48*, then the one without [ * ] would be considered.

BOWLING :

This is a new addition which was not considered earlier.
The main crux is to be able to derive the MAX and MIN from different sheets and different Rows.

Best bowling Figures--This would be based on the Number of wickets and the number of runs and should be displayed as 3-36 which means that the bowler took 3 wickets at the cost of 36 runs in a match..

But if there are more than one occasion of taking the same number of wickets then the runs conceded should be given weightage..

Example :
1. 3-39
2. 3-26

Naturally 3-26 should be the Best bowling figures.

Again if the runs are same then the number of wickets would be considered in the Best bowling figures.

1. 3-36
2. 1-36

Naturally 3-36 should be the Best bowling figures.

There are two files Cricket-III and Dynamic Addition-4.0Now the Cricket file is to get the information on Cricket as well as to be aware of codes for including the "*" in the Runs in the same sheet.

However, the Actual sheet is the Dynamic Addition...
I need help on Dynamic Addition and the Cricket-III is just for reference..

I have put Call-outs to explain wherever there's a requirement..

Seven things are required shown by the call-outs--

Not Out
Innings
Runs
Average
Highest Score
Lowest Score
Best Bowling figures

[daddylonglegs]

I haven't looked at everything but for best bowling figures you could use a formula like

=MAX(Data!J3:J7)&"-"&MIN(IF(Data!J3:J7=MAX(Data!J3:J7),Data!G3:G7 ))

needs to be confirmed with CTRL+SHIFT+ENTER

Obviously you can use dynamic named ranges if you wish

....and "not out".....

=COUNTIF(Runs,"*~*")

[all4excel]

Dear Daddy,

This works the way I wanted...however this is for the Cricket-III file, I want the same logic to be intertwined with Different Tabs and Different Cells....

For Ex:-
In Dynamic Addition-4.0 file, If I am picking "Andrew Silmoure", now his information is placed on different tabs in different sheets...So that's challenging to keep a track of all the Tabs and also the different rows and then also keep a track of the Scores where he was Not out...

I have attached the Cricket file for understanding but I need help on the Dynamic Addition-4.0 file.


Im using this code provided by Nrage21-

[ SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B3,INDIRECT("'"&Tabnames&"'!E3:E13"))) ]

This does give me the total runs but it skips the scores with "*".

I want to get the Count of all Not Out Innnings to get the Not Out,Average,Innings,Highest Score,Lowest Score..

We already have the logic on a single sheet same row, we need to be able to replicate this on Multiple Sheets where the Data is also spread on different rows..

There would be a lot of SUMPRODUCT functionality...I know for sure but not able to do that till now...

-----------------------------------------------------------------------
This definitely works in a single-sheet but cannot be put in Dynamic Addition-4.0...

=MAX(Data!J3:J7)&"-"&MIN(IF(Data!J3:J7=MAX(Data!J3:J7),Data!G3:G7 ))

needs to be confirmed with CTRL+SHIFT+ENTER

Obviously you can use dynamic named ranges if you wish

....and "not out".....

This works but can u please explain this code...[ "*~*" ]

=COUNTIF(Runs,"*~*")


Thanks a lot

all4excel

[all4excel]

Bump........Bump................................

[nrage21]

all4excel,

I thought about your project the whole weekend, and concluded that the initial design might not be user-friendly due to the complexity of coming up with appropriate worksheet functions. They are not impossible, but very complex.

In other words, we got a group of worksheets (and soon there will be more) that feed off one or two (summary sheet/matches sheet) summary sheets... or an all-to-one approach.

What I was thinking, is that if we reverse the approach... or a one-to-all approach, conceptualization of worksheet functions and usability might become as easy as pie to accomplish.

Perhaps the attached sample file will be able to illustrate better.

Notice how the 'Main' sheet feeds off all the other sheets and creating new sheets is done effortlessly (pay special attention to cell A1 in an opponent's sheet, the cell finds and uses the worksheet name. The rest of the formulas follow a 3 condition rule (opponent, match#, and player name) and find the appropriate value per category using a combination of index, matching, and sumproduct worksheet/array functions.

Anyhow, I left some formulas empty so you can do them yourself and enjoy the fruit of your labor. Please feel free to ask the forum for any questions you might have.

Regards,
nrage21

[all4excel]

I thought about your project the whole weekend, and concluded that the initial design might not be user-friendly due to the complexity of coming up with appropriate worksheet functions. They are not impossible, but very complex.
Thanks for taking your valuable time to ponder over my file..I really aprreciate your willingness to help as well as your passion.

However, I would like to inform you that it would not be possible to have a contiguos arrangement as in your Main file. The reason for that is we woule be recording the scores in different sheets and would not be tampering the entire file..
I agree that this would defintely be a good idea in terms of getting all the details the way it's desired..However it would be defeating the purpose of consolidation..

You know the code provided by you to extract and Sum data across different sheets was excellent maybe that actually drove me to expect the unexpected or something very complex..

I wish someone had an anser to this problem..

I have been able to get an automatic list of Tabnames which works normally without the Asterik but to get the code inclusing the asterik is very tedious..

Even for the best Bowling figures getting from one sheet is possible but replicating for Multiple sheets is some task..

Thaks for all your help, In case you still come across any other alternative please let me know...

[nrage21]

all4excel,
If multiple users are entering information, perhaps google.com/docs forms is the tool for you.

In a few words, you create a form with all the cricket fields and publish it inviting all the editors. Then at a specific time, you can export the file (that would be your main sheet) and insert it right onto the workbook with all the calculations.

I manage 20+ marketing teams and they all have to enter information daily... google forms have allowed me to see the information in real time in a centralized place and then export it to a workbook where I do all of my calcs...rather than emailing 20+ different sheets to the field constantly.

Regards,
nrage21

[all4excel]

Nrage,

I would like to know more about those forms which you are talking about..

But the reason I said of not having everthing in one file is to avoid people tampering the data in that file..

But I think there would be a way though its very complex and I think the Gurus like Daddylonglegs or NBVC would surely have..

I think Daddy has a great command over SUMPRODUCT..

Wish I get a solution..

[nrage21]

No one, but you, will have access to the main file. They will only be able to provide information through a form.

[NBVC]

I think for summing the numbers with/without *'s over multiple sheets for a particular player, you would be best to add that helper column I suggested in this thread: http://www.excelforum.com/showpost.p...97&postcount=7

You can hide the column from view if you want.... but the Sumproduct(Sumif()) formula does not allow to sum the quantities with *'s....

[all4excel]

Dear NBVC and Nrage..

NBVC - Do not think that I have forgotten that alternative, I very well have that in my mind..I just wanted to use it as a last resort..

Now I have just made a slight change in your code...

[ IF($E3="","",IF(ISNUMBER(RIGHT($E3)+0),$E3,LEFT($E3,LEN($E3)-1)+0)) ]

In your code the colored portion wa a zero...

I am just putting the same value in E3 if its a plain number..

But how would I get the count for Not Out? to get Average,Max,Min and how do I display the max and the Min with an "*" if the Btasman was Not out?

And how about the Best bowling Figures..

NRage - Brother.. I would also try this alternative of the main file..

[all4excel]

Dear NBVC,

I tried the approach of the Helper column, I hope you know that Im seeking help on the Dynamic Addition-4.0 file and the Cricket-III file was for reference..

I reverted your code of placing a zero..

Some doubts even after using the Helper Column?

1. I get the the total runs by adding the two sumproducts for the columns E and Column F as shown below..

[ SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B3,INDIRECT("'"&Tabnames&"'!E3:E13")))+SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B3,INDIRECT("'"&Tabnames&"'!F3:F13"))) ]

2. Now how do I differentiate between actual zero scores and 0 scores when the Batsman was Not out without scoring any runs like 0* and actual 0 (nought).

3. How would I count the number of instances when the Batsman was Not out in the Dynamic Addition-4.0 file as thats the main crux..

I would get answers to calculating Average,Innings,Strike rate,Highest Score,Lowest Score by getting the above..

In Bowling, need help on the Best Bowling figures from different sheets...

Please someone please help me...

I would be really greatful..

[NBVC]

2. Now how do I differentiate between actual zero scores and 0 scores when the Batsman was Not out without scoring any runs like 0* and actual 0 (nought).

Not exactly sure what you mean? Why and where does this differentiation have to me made?

3. How would I count the number of instances when the Batsman was Not out in the Dynamic Addition-4.0 file as thats the main crux..

Didn't daddylonglegs give you that answer earlier? i.e. =COUNTIF(Runs,"*~*")

This means anything that starts with anything and ends with an * will be counted. Replace Runs with actual range to count.

NOTE: Please do not ask for too much in one thread... each thread should be for one question. If you ask for too much at once, you will find that people are more reluctant to help because they don't want it to suddenly become their project...which they probably do not want to inherit!

[all4excel]

Not exactly sure what you mean? Why and where does this differentiation have to me made?


Didn't daddylonglegs give you that answer earlier? i.e. =COUNTIF(Runs,"*~*")

This means anything that starts with anything and ends with an * will be counted. Replace Runs with actual range to count.

NOTE: Please do not ask for too much in one thread... each thread should be for one question. If you ask for too much at once, you will find that people are more reluctant to help because they don't want it to suddenly become their project...which they probably do not want to inherit!

I have already explained in all my threads that the codes provided work for the Cricket-III file but I need help on the Dynamic Addition-4.0 file..

This code [ =COUNTIF(Runs,"*~*") ] works but how do i make it work on Dynamic Addition-4.0 file..?

Similarly your code works with the Helper column again in the Cricket-III file but how do I get the count of "Not Out" for the Dynamic Addition-4.0 file..?

[NBVC]

The formulas should work for both files...

You need to adjust ranges and/or sheetnames in the formulas.

Why did you include both files, if you only need help on 1 file... that only confuses everyone!!!

[all4excel]

The formulas should work for both files...

You need to adjust ranges and/or sheetnames in the formulas.

Why did you include both files, if you only need help on 1 file... that only confuses everyone!!!

Dear NBVC,

I had added the Cricket file for others to understand more about cricket as well as there were some codes already in the file which were working and which needed to be tweaked for Multiple Sheets and Different Rows Concepts..

I had clearly mentioned that in all threads to avoid any confusion..
The reason to choose the files was to help the person helping me to work and avoid him working from scratch as this code was already available.But needed a bit of twist..

Moreover Nrage in order to help me put this file in a different thread and you provided solution based on the Cricket file..

Nrage had clearly mentioned though that this project belongs to me...
http://www.excelforum.com/showthread.php?t=638139

Now do u want me to start a new thread? by only adding the Dynamic Addition-4.0 file

[NBVC]

...

Now do u want me to start a new thread? by only adding the Dynamic Addition-4.0 file

I think you should... but you should only ask 1 question per thread...when you get the solution for that, start a new thread with another different question. You can also add hyperlinks to your other thread.. in case anyone is interested.

[all4excel]

Please help me in getting the Average...in this file..

Total Runs ---Total Runs including numbers with and without asterik ( * )

Not Out--Only counting the instances when the Batsman was "Not-out".

Average--Total Runs / ( Total Matches played - Not Out instances )

I am attaching the File...

[all4excel]

I have put this question in Mr Excel

http://www.mrexcel.com/forum/showthr...66#post1528666

[Gearcutter]

Hi, The big problem with this spreadsheet is the lack of consistency the cricketers names are a case in point, Hoggard is Hoggard fine, Andrew Silvermoure is again fine as long as he remains Andrew Silvermoure, in the case of Alwyn R on one sheet he becomes just plain Alwyn no "R". I took the liberty of of renaming Alwyn as Alwyn R otherwise accurite results can't be obtained.I took liberties with 2 other inconsistaces,on the JP Morgan sheet in column c the not out column in everyother instance you showed either a 1 or a 0 depending on what column D showed in C12 you showed "Not out" and in C13 "DNB". I put a 1 in C12 and left C13 blank.This allowed me to calculate the number of times a batsman was "Not out". This I did with The function Sumif. Perhaps you could explain to us just why you have used the "*" in the context that you have, there does'nt seem any logical reason for it. For people that don't know Cricket Stats, a score like say 186* would usually be found in the column headed "Highest score" and would normally mean, 186 Not out, as we have a "Not out" column and a "How out" column why would you put a "*" alongside runs in the "Runs" column?
Anyway to accommadate the "*" I had to put a "Runs" column at col v to separate the "*" from the runs. This then enabled me to count all the runs including the 14* that Andrew Silvermoure was missing. hope this meets with your approval. can't wait for the next chapter?

[all4excel]

Hi, The big problem with this spreadsheet is the lack of consistency the cricketers names are a case in point, Hoggard is Hoggard fine, Andrew Silvermoure is again fine as long as he remains Andrew Silvermoure, in the case of Alwyn R on one sheet he becomes just plain Alwyn no "R". I took the liberty of of renaming Alwyn as Alwyn R otherwise accurite results can't be obtained.I took liberties with 2 other inconsistaces,on the JP Morgan sheet in column c the not out column in everyother instance you showed either a 1 or a 0 depending on what column D showed in C12 you showed "Not out" and in C13 "DNB". I put a 1 in C12 and left C13 blank.This allowed me to calculate the number of times a batsman was "Not out". This I did with The function Sumif. Perhaps you could explain to us just why you have used the "*" in the context that you have, there does'nt seem any logical reason for it. For people that don't know Cricket Stats, a score like say 186* would usually be found in the column headed "Highest score" and would normally mean, 186 Not out, as we have a "Not out" column and a "How out" column why would you put a "*" alongside runs in the "Runs" column?
Anyway to accommadate the "*" I had to put a "Runs" column at col v to separate the "*" from the runs. This then enabled me to count all the runs including the 14* that Andrew Silvermoure was missing. hope this meets with your approval. can't wait for the next chapter?

Dear Gearcutter,

I apologize for those two columns, The Not Out column should not have been there as the Score i.e. Runs suffixed by * would always be an indicator and Ideally the "How Out" column would be Blank if the Batsman Did not BAT..

This is my mistake and I sincerely apologise for that as you put in your efforts..

Regarding the inconsistancies in the Names that can be avoided as Iwould keep an extra tab where there would be a List of all the players in excess of 11 participating in the Match, there would be a possibility a different player would play and not the same eleven play depending on his Fitness and other reasons..

The Main crux is to get the number of Runs form all the Worksheet with the Asterik ( * ) + Located at different Positions + The sheets would always increase..


I already have the formula to get the Total runs for a player when there is no asterik involved and this works even when you add any sheet/tab as long as you add the name in the Column A below Opponents..

I have defined this as Tabnames
[ OFFSET(Consolidation!$A$2,0,0,COUNTA(Consolidation!$A:$A)-1,1) ]

This helps me to get the Any new Sheet added in the Formula...

My working formula-

[ SUMPRODUCT(SUMIF(INDIRECT("'"&Tabnames&"'!A3:A13"),$B2,INDIRECT("'"&Tabnames&"'!F3:F13"))) ]

But this fails when there is an asterik involved...

As per your formula, it cannot take care of any addition unless explicitly updated in the formula..

So keeping the above same, i would need a robust formula to Count the number of Not outs and Runs..

[all4excel]

Is it possible to get just the MAX?

Im looking forward to get Highest Score in Batting for a Player in the Consolidation File from different Sheets located at different Rows..

THe formula should also work when any New Sheet is Added..

[CSWillow]

The formula that you have quoted =MAX(Data!J3:J7)&"-"&MIN(IF(Data!J3:J7=MAX(Data!J3:J7),Data!G3:G7 )) works perfectly well for data that is listed in columns, but I have a further complication in the way in which I list bowling figures in my cricket stats worksheet.
I list batting & bowling stats for each player in rows, rather than in columns.
In my worksheet there are stats for 15 matches, so the bowling figures for match 1 (e.g. 45 (runs conceded) & 3 (wickets taken) for 1st innings and 12 & 2 for 2nd innings) are posted in cells j4,k4 & j5,k5. For match 2 the relevant cells are s4,t4 & s5,t5 and so on.
The complete list for all cells in which the runs & wickets are listed is - j4,k4 & j5,k5 ; s4,t4 & s5,t5 ; ab4,ac4 & ab5,ac5 ; ak4,al4 & ak5,al5 ; at4,au4 & at5,au5 ; bc4,bd4 & bc5,bd5 ; bl4,bm4 & bl5,bm5 ; bu4,bv4 & bu5,bv5 ; cd4,ce4 & cd5,ce5 ; cm4,cn4 & cm5,cn5 ; cv4,cw4 & cv5,cw5 ; de4,df4 & de5,df5 ; dn4,do4 & dn5.do5 ; dw4,dx4 & dw5,dx5 ; ef4,eg4 & ef5,eg5.

Is there any way of adjusting the abovementioned formula to calculate the player's best bowling figures over the course of these 15 matches.
Any help would be appreciated.