Search results

Hi Narayan. That was incredibly, amazingly, awesome! Your formulas are so simple, yet they work completely perfectly. I can even understand exactly what they are doing! THANK YOU SO MUCH! Any chance that you can apply your obvious ability to my other issue (I'm still waiting for a reply on...

Thanks. Will let you know.

That sounds like it might work, Narayan. What do you think, Kaushik? The link to the sample workbook appears earlier in this post if you want to experiment. However, by way of summary, we have purely numeric data in one column (representing the scores made by batsmen), while another column...

Kaushik, you are absolutely right. Sorry. I misread what A10 was displaying, and was expecting that "out" would then appear at F3. Narayan, you are correct. What we want is that if either of a player's highest or second-highest score was "not out", then the formula must display that score...

Hi Kaushik - I'm very pleased to see you still wrestling with this problem. Your macro didn't seem to work when I downloaded the workbook though - changing E1 from "A" to "B" gave me B's highest score in F1, but G1 continued to display "not out". A10 also gave an incorrect setting. I have...

Yes, of course. I placed your data in exactly the same cell references as you mentioned in your post, and everything worked fine. Then, when I added further data (rows) beyond B5 and C5 (and altered the range in each formula accordingly), the formulas cease to function - the formula for the...

Hi Kaushik. I did manage to get Luke M's formula adapted to the needs of my Career Summary worksheet. It looks like this: =INDEX('Indiv Performances'!Y:Y,MAX(ROW(Indiv_Performances[How Out])*(Indiv_Performances[How Out]=B5)*(Indiv_Performances[Inngs]=LARGE(IF(Indiv_Performances[How...

Can I make one other request as well please (I know I'm pushing my luck)? Is there any way to alter the data validation dropdown list setup that I have as C3 and D3 on the "Career Summary" worksheet? As it now stands I can only select one option at a time for both the "Team" and the...

Thanks Kaushik. Any chance you could convert this to our worksheet though - our scenario is a little more complicated, in as far as we also have to sift out the correct player's name as well (in your generic example, you used "A" as the player name throughout, so it wasn't an issue). I...

Correct yes. The not out asterisk applies equally to second-highest scores (L3) as it does to highest scores (K3). The exact same set of rules applies to both cells, with the exception that the highest score must appear in K3., and the second-highest in L3. In addition, the same sorting...

I did try that with the copied and adapted formula for L3, changing it to a plain IF formula, but obviously I'm doing something wrong because it isn't correcting itself to 0*. The problem seems to be with the array formula though (which I can't figure out, being my Excel weakness), because it...

That seems to work quite nicely, thank you. I have even managed to adapt this approach to do the same for a player's second-highest score in L3, should that also be a not out innings. My Career Summary workbook is starting to look very impressive! Just one thing though. You include in your...

Can I post another request please? Cells K3 and L3 of the Career Summary worksheet display the highest and second-highest scores made in an innings by a batsman. Can you perhaps tweak these formulas too so that they display an asterisk after the score if the innings finished as "not out"...

Thank you - you too. Looking forward to hearing from you again!

Unfortunately my cricket duties are a hobby, not a profession - I would also love to do that for a living!! I figured that you were probably a cricket-lover - India's love affair with the sport is well-known, and your continued involvement in this post is proof of your passion as well! I will...

Kaushik, I'm from Cape Town, South Africa. I'm the scorer / statistician for the First XI of my cricket club, Cape Town Cricket Club, and am putting this spreadsheet together to help me generate all the stats and records I need for my season review booklet that I compile after every season...

You are awesome, Kaushik!! Thanks very much for your dedication to solving this for me. I will study what you did and report back if everything is working as it should.

Exactly right Kaushik, but on a match (multi-row) rather than an innings (single row)basis.

Kaushik That did work for R7, thanks. I'm not sure about the dropdown on the Career Summary worksheet though, because it only seems to control what is displayed in that one cell R7. Is there not a way that selectinbg a player in B3 will auto update all of the Career Summary cells, including...

NARAYANK991 The difference is probably brought about by the Team dropdown list - Carl Mellors played 14 games for Cape Town and one against them (for Kuils River), making 15 in total.

Hi Kaushik. Thanks very much for your efforts to help me out! Your formula for R7 is not working correctly though. There are in fact two instances of bowlers taking ten or more wickets in a match - Nick Preston took 6-22 in the first innings and 4-5 in the second innings of Match #2, giving...

Okay. I've managed to get formulas to work for all but two of the cells in the "Career Summary" worksheet - K7 and R7 (see dropbox link to spreadsheet at bottom). Can somebody please help me with formulas for those two cells please? Cell K7 must find all rows in the "Indiv Performances"...

Hi kaushik03. Did you get the attached sample worksheet? Are you going to link it for me please? Thanks very much.

Hi. I am a trainer who uses Excel probably more for personal reasons than work-related ones, and it's probably my favourite application ever! I find it fascinating, and sites like this make one realise just how amazingly extensive Excel is. Thus I have discovered that I am not even...

That is wonderful of you, kaushik03! Thanks very much!! I have emailed the sample spreadsheet to you.