Excel 2007 : creating a formula that addresses three separate columns

[Grumpy88]

Hi.

I need assistance please in creating a formula that accesses and displays data from three separate columns. I am setting up separate Excel 2007 worksheets for each of a bunch of cricket players (for those of you familiar with the sport of cricket), in which I enter their bowling figures on the "Bowling" worksheet, in a separate row for each innings. The three columns in question are "Type", in which I enter the competition format of that particular match (one of either "Two-Day", "Ltd Overs" or "AMA20-20"), "Wkts" (in which I enter the number of wickets taken in that innings) and "Runs" (in which I enter the number of runs conceded in that innings).

As I thus populate the "Bowling" worksheet, with the cricketers playing all three types of matches through each season, there are multiple occurrences of the same Match Type, each with different Wickets taken and related Runs conceded in the row concerned.

I attach an example of one player's worksheet, for investigation. The formula that I need is a concatenated one to place after the hyphen in cells L26, L30 and L34 of the worksheet "Career Stats" (this worksheet uses formulas to summarise the content of the "Bowling" and other worksheets), which returns the player's best innings bowling figures in each competition type - "best" being defined as the highest number of wickets taken in an innings for the least number of runs conceded. For the uninitiated, this means that taking 1 wicket for 50 runs is a better performance than taking 0 wickets for 20 runs, but if the same number of wickets are taken on more than one occasion, then the one with the least number of runs conceded is the better return (i.e. 2 for 17 are better figures than 2 for 30, for example).

I can get Excel to display the maximum number of wickets taken in an innings for each Match Type using DMAX, but I can't get my head around the formula needed to display the correct related runs value. Using VLOOKUP didn't help, because it only returns the Runs amount linked to the first instance of the maximum Wkts amount. Thus, if the player later took the same number of wickets but conceded less runs, the formula didn't pick up the new lower runs amount. DMIN and MIN also didn't help, as I couldn't get them to relate to all three criteria (Type, Wkts and Runs).

Can anybody help with this formula please??

Thanks very much!

[DonkeyOte]

Calculating BB can be a pain given the need for fewer runs to take precedence where wkts are equal.

Looking at your file I would suggest the following route which is something I do myself to avoid need for expensive repetitive calcs:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

repeat the above for other types modifying DMAX criteria ranges accordingly

Based on the sample the above would return:

6-45 | 3-13 | 2-25

respectively

[Grumpy88]

Hi DonkeyOte.

Thanks for the quick response - I do appreciate it!

I followed your instructions (a bit scary / intimidating for me, as I have no real idea as to what those formulas are actually doing), but get a #VALUE result in the three "Career Stats" worksheet cells (L26, L30 and L34). I am reattaching the amended spreadsheet for you to inspect again, in case I misunderstood your instructions.

[DonkeyOte]

Sorry, I should have added that you need to add BB header value to Bowling!AF1 (for sake of DMAX)

[DonkeyOte]

Should also have said - for Career (L22) just use:

Please Login or Register  to view this content.

[Grumpy88]

Awesome stuff!! Works like a treat! I must spend some time investigating those two formulas of yours to try and figure out how they work for future reference. Thank you very much - I sat for hours last night trying to get that right.

A last question, if I may? What formula can I put in "Career Stats" L22 that will automatically display the best bowling figures out of the three competition formats?

P.S. Assuming that I can figure out how they work, will the same formulas be good for displaying best bowling figures in a match too (i.e. in Two-Day games only, considering the total bowling return over both innings)?

[DonkeyOte]

What formula can I put in "Career Stats" L22 that will automatically display the best bowling figures out of the three competition formats?

see my prior post (must have crossed in cyberspace)

I must spend some time investigating those two formulas of yours to try and figure out how they work for future reference

In terms of the formula, it's basically just creating a number for each bowling record where integer = wickets and decimal = runs conceded

The complexity stems from the fact that the lower the runs conceded the greater we want the decimal value to be, eg:

6-25 is better than 6-35

On that basis we divide the runs by 1000 and subtract result from 1

6 + 1 - 25/1000 -> 6.975
6 + 1 - 35/1000 -> 6.965

we then know that the biggest number is the best performance

the only other thing we need to handle in this instance is 0 conceded, eg 6-0 (dream)

6 + 1 - 0/1000 -> 7.000

this would be wrong as it would imply we'd taken 7 wickets

to account for this possibility we use the max of runs and 0.1 as our numerator

6 + 1 - MAX(0,0.1)/1000 -> 6.9999


Once we have the values set up we can use MAX to retrieve best bowling but obviously 6.975 is not much use to us - we need to convert that back into 6-25

Wickets:
we can use the INT of our MAX -> 6

Runs:
we can take the decimal remainder of our MAX

=MOD(6.975,1) -> 0.975

subtract that value from 1

=1-0.975 -> 0.025

multiply by 1000 (the same divisor we used earlier)

=0.025*1000 -> 25

we use ROUND to handle significance issues and also to handle our 0 runs conceded (ie 0.0001) such that we round our multiplication to nearest whole number, eg:

=ROUND(0.0001*1000,0) -> ROUND(0.1,0) -> 0

the above is just one approach - i'm sure there are others that are more efficient but I tend to use the above myself as chief stat-o for my club.

will the same formulas be good for displaying best bowling figures in a match too

The BB figures being generated in Col AF are specific only to the record to which they are assigned.

On that basis you can use those values to do conduct whatever analysis you like (type, match, month, venue etc...)
The key is not the BB calculation (Col AF) but rather identifying which of those values to include in your (D)MAX calculations.

[Grumpy88]

Brilliant - I would never thought of taking that approach. And here I thought I was a bit handy with Excel! Live and learn!!

[DonkeyOte]

It's perhaps worth stating that you can use the whole integer.decimal for other things too - eg best batting performance

You can convert n.o to 0.1 for ex. and add to runs

You can then retrieve the Max and handle appropriately, eg:

Please Login or Register  to view this content.

[Grumpy88]

How happy am I that I registered with this forum! And what were the odds of my post being read by another club cricket statistician, especially in an American-dominated industry? Totally amazing.

[Johnson1980]

found this very, very useful, thanks. Just have one question. I have a couple of bowlers who have taken no wickets but instead of displaying 0-22 my career stats for that year show 0-0. Any thoughts?

Thank you

[arlu1201]

Johnson1980,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.