Extracting numbers from text and then adding together

[Rich N]

Hi,
Sorry for posting what seems like such a ridiculously simple query, but despite spending several hours poring through old MrExcel message boards I'm afraid I still need help.

I am using Excel to collate the averages for my cricket team. All the players are listed in column A, followed by their scores for each individual game throughout the year and ending with a total runs column for the season.

My problem arises when I have to give an indication that a player scored runs but was "not out", which impacts on the overall averages because a player who is not out is credited with the runs scored but not charged with an innings. So a player who made 15 not out is credited with "15no" in my workbook.

As a simple example: Player's name (Barry Smith) appears in A1, then his five scores: 23, 15no, 35, 125no, 2 in cells A2:A6. SUM($A2:$A6) entered in A7 ignores the cells that also include text (the "nos") and gives me a total of 60. Is there a formula I can enter in A7 to disregard the "nos" and just read the digits in those cells to come up with the grand total of 200? The run totals can be 1, 2 or 3 digits and I can split the "no" from the number (i.e. "15 no" instead of "15no") if this makes things easier.

Many thanks.
Richard Neale.

 

[Peter_SSs]

Rich N

Another suggestion for highest score - just a very slight modification to Domenic's suggestion:
=INDEX(J5:AG,MATCH(MAX(SUBSTITUTE("0"&J5:AG5,"no","")+0)&"*",J5:AG&" ",0))
confirmed with Ctrl+Shift+Enter

However, I am half expecting you to come back and say "But if a player has high scores of 135 and 135no the result should be returned as 135no". I think all our suggestions so far will return whichever of these scores first (left to right).

 

[Rich N]

Hi Peter, and thank you again.

"However, I am half expecting you to come back and say..."

It's a thought, of course...but have no fear, I am absolutely thrilled with the solutions already so generously provided and know when not to push my luck!

Your help has been invaluable, guys, I would NEVER have got there on my own. Thanks a million!

RN.

 

[Peter_SSs]

Rich N

a) Array formulas (Ctrl+shift+Enter type) eat up a fair amount of resources and this can eventually make a noticeable impact on the sheet performance.
b) The suggested solutions so far make a lot of use of the SUBSTITUTE function to keep removing the "no" from the scores to enable calculations. Doing this so many times for each player seems inefficient to me.
c) Cricket is a passion of mine.

For the above reasons, I have had another look at this problem and wondered if you would consider an alternative approach? If you want to stick with what you have that's fine also. My new approach uses some of the ideas already used but also incorporates the idea (suggested by jonmo1 right back near the beginning) of removing the "no" from the scores to make manipulation easier.

For my example, I have gone back to just 5 scores with data starting in A1. You could adjust as necessary. I have added a count of Centuries and also addressed the issue of equal top scores when at least one of them is a "no" (Tom). Although the layout is a little more complicated than previously, I think the logic and the formulas are much simpler. Anyway, see what you think.

1. Allow two rows for each player. (Another option would be to put the 'extra' information on the same row as the other player data but further across to the right. Let me know if you want to investigate this and need help.)
2. Formula in B3 (copied across to F3): =IF(B2="","",SUBSTITUTE(B2,"no","")+0)
3. G3: =SUM(B3:F3)
4. H3: =IF(COUNT(B2:F2)=0,"-",ROUND(G3/COUNTIF(B2:F2,">=0"),2))
5. I3: =MAX(B3:F3)&IF(ISNUMBER(MATCH(MAX(B3:F3)&"no",B2:F2,0)),"no","")
6. J3: =COUNTIF(B3:F3,">=100")
7. K3: =COUNTIF(B3:F3,">=50")-J3
8. Select B3:K3 and copy to rows 5, 7, 9 etc

Mr Excel 06 10 29.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Player	Game 1	Game 2	Game 3	Game 4	Game 5	Total	Average	High Score	100s	50s
2	Tom	135		35	135no	2
3		135		35	135	2	307	102.33	135no	2	0
4	Tim	12	55	0	101	8
5		12	55	0	101	8	176	35.20	101	1	1
6	Ted	15no	66no		0no
7		15	66		0		81	-	66no	0	1
8	Todd	0	0	0no	0	0
9		0	0	0	0	0	0	0.00	0no	0	0
10	Trev	66	56no		15	50
11		66	56		15	50	187	62.33	66	0	3
12
Cricket Averages

 

[Rich N]

Peter

I quite understand what you are saying with the arrays using up resources and slowing things down.

Many thanks for your alternative, which is indeed much simpler. Even I can understand the formulae, that's now simple it is!

Without wishing to sound ungrateful, I have come up with a layout that I like and that will make it easier on the eye for my team-mates when I get around to mailing the finished document to them.

I can just about get a completed worksheet on one screen at present, the problem with an extra line for each player is that we have a big squad (39 is most players used in one season so far in the stats that I have crunched). 39 extra lines is another page-worth of info, and while scrolling down is not a problem, it just is not as aesthetically pleasing.

My intention is to then have 16 seasons of information in one document with a master first page which will collate lifetime batting (and, in due course, bowling) averages and give an instant update when each future game is completed.

I'll happily send you a copy of the document when complete should it be of any use to you as a starting point before you work your magic on it.

Thanks again for your input and well done to Aussies today on a most impressive victory. We (England) could be in trouble in a month or so's time!

Rich.

 

[Peter_SSs]

Rich

No offense taken re layout. Though I haven't seen your layout or had it described, a couple more ideas for you to consider:

1. The actual 'result' formulas (columns G to K in my layout) could be moved up one row to be on the main player line. After you have entered all your data, you could hide rows 3, 5, 7 etc so that the user just sees the actual scores and results.

2. Possibly a better solution is to move the row 3, 5, 7 etc formulas up to rows 2, 4, 6 etc but over to the right out of the road (say column AA onwards?). Again after data entry is complete, these columns could be hidden. This way each player uses just one line and you only have one block on the right to hide. Not sure that I have described this one too well but my little sample would look like this with columns L:P (green area) eventually hidden.

Mr Excel 06 10 29.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Player	Game 1	Game 2	Game 3	Game 4	Game 5	Total	Average	High Score	100s	50s	Game 1	Game 2	Game 3	Game 4	Game 5
2	Tom	135		35	135no	2	307	102.33	135no	2	0	135		35	135	2
3	Tim	12	55	0	101	8	176	35.20	101	1	1	12	55	0	101	8
4	Ted	15no	66no		0no		81	-	66no	0	1	15	66		0
5	Todd	0	0	0no	0	0	0	0.00	0no	0	0	0	0	0	0	0
6	Trev	66	56no		15	50	187	62.33	66	0	3	66	56		15	50
Cricket Averages (2)

We (England) could be in trouble in a month or so's time!

I'm hoping so, but if it is anything like the last series in England it should be a good contest. I'm looking forward to it - first test starts 23 Nov.

 

[Rich N]

Hi, not sure if I should have started another topic for this six months on from my original post, but I really could do with a little guidance with the following problem please.

Having input 17 seasons worth of cricket data, I have, with the help of the kind people on this messageboard been able to collate season-by-season and cumulative batting and bowling averages.

I am just making a few refinements and additions, one of which is to produce an innings-by-innings list of each of our regular players, showing opposition, date, the number in the batting order he batted in each innings and the score made, as per the table below, hopefully identifying patterns to show such things as who performs better as an opening batsmen and who should really be a tailender.

LINERS_BATTING.xls
	F	G	H	I
2	Oppo	Date	Batted	Score
3	LindfieldII	07/05/06	1	100
4	Dormansland	14/05/06	5	10
5	HorshamTrin	28/05/06	5	20
6	SouthernCross	04/06/06	8	30
7	Ardingly	11/06/06	3	5
8	Nuthurst	18/06/06	3	15
9	IslandBakeries	02/07/06	3	15*
10	Southwater	15/06/06	3	25*
11	Lindfield	06/07/06	4	42
PBryant

The aim is to produce averages for each batsmen for every spot in the batting order he has batted. For instance, in the table above if I use the formula =SUMIF(H2:H11,"5",I2:I11) I can find the total runs a batsmen has made batting at No5 (which is 30 runs, the sum of I4 and I5). OK, no problem, even for an Excel dummy like me.

The problem arises when I have a batsmen who made a total but was not out, which I indicate by an asterisk, ie. 15* at I9 in the table signifies 15 not out.

For the purposes of my calculations I would like Excel to ignore the asterisks and just read 15* as 15, so that if I input the formula =SUMIF(H3:H11,"3",I3:I11) it would add 5, 15, 15* and 25* and total 60.

I have tried various combinations of incorporating (H3:H11,"3",I3:I11) with (--SUBSTITUTE("0"&I3:I11,"*","") but as I am only guessing at what I am doing without fully understanding the workings of the formula it is little surprise that it appears to have got me beaten.

Any help anyone can give would be much appreciated.

P.S. Peter_SSs, if you happen to read this, thank you for your previous help, the thrashing in the Ashes and congraulations in winning the World Cup. Your Aussies are probably better at riding pedaloes than us Brits as well!

 

[Peter_SSs]

Rich

Let's deal with the important part first:

Peter_SSs, if you happen to read this, thank you for your previous help, the thrashing in the Ashes and congraulations in winning the World Cup. Your Aussies are probably better at riding pedaloes than us Brits as well!

Well yes I did read this and what a turn-around from the previous ashes series!! It was all good looking from this side of the world but must have been quite distressing from over there.

How quickly things can go wrong. I think Freddie should have some pedaloe lessons in the off-season!

OK, now to your spreadsheet. It looks like you were thinking along the right lines but just didn't quite fluke the right combination. Try this.

L3: =SUMPRODUCT(--($H$3:$H$11=K3),SUBSTITUTE($I$3:$I$11,"*","")+0)
M3: =IF(SUMPRODUCT(--($H$3:$H$11=K3),--(RIGHT($I$3:$I$11,1)<>"*"))=0,"N/A",L3/SUMPRODUCT(--($H$3:$H$11=K3),--(RIGHT($I$3:$I$11,1)<>"*")))
This is quite a long formula as it has to cope if the batsman has never been dismissed batting at a particular position and therefore has no average for that position. (See, for example, I changed the last score to 42 no)

Copy both formulas down.

BTW, I think this guy should get another shot at opening the innings.

Mr Excel.xls
	E	F	G	H	I	J	K	L	M	N
1
2		Oppo	Date	Batted	Score		Pos	Total	Av
3		Lindfield II	7/05/2006	1	100		1	100	100
4		Dormansland	14/05/2006	5	10		3	60	30
5		Horsham Trin	28/05/2006	5	20		4	42	N/A
6		Southern Cross	4/06/2006	8	30		5	30	15
7		Ardingly	11/06/2006	3	5		8	30	30
8		Nuthurst	18/06/2006	3	15
9		Island Bakeries	2/07/2006	3	15*
10		Southwater	15/06/2006	3	25*
11		Lindfield	6/07/2006	4	42*
12
PBryant