Best way to perform calculations on a list of numbers in a cell?

[JenniferMurphy]

I did a search and came up with the solution below to perform calculations on a list of numbers in a single cell. Is there a better way?

Mr Excel.xlsx
	B	C	D
4	Weights	Average	Count
5	65 64 68 62 69 70	66.33	6
6	64 65 64 65 64 65 64 65	64.50	8
7	55	55.00	1
8	64 65 66	65.00	3
Cell Average

Cell Formulas
Range	Formula
C5:C8	C5	=AVERAGE(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
D5:D8	D5	=COUNT(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))

Thanks

 

[6StringJazzer]

That's a pretty good way to do it. The only thing better would be to put each number in one cell.

 

[JenniferMurphy]

That's a pretty good way to do it. The only thing better would be to put each number in one cell.

The problem with that is that the number of numbers varies. I suppose I could create enough columns for the maximum number, but that would take up more space.

 

[AhoyNC]

Another option (probably not better) would be to use a double negative in front of TEXTSPLIT.

Book1
	A	B	C
1	Weights	Average	Count
2	65 64 68 62 69 70	66.33333333	6
3	64 65 64 65 64 65 64 65	64.5	8
4	55	55	1
5	64 65 66	65	3
Sheet4

Cell Formulas
Range	Formula
B2:B5	B2	=AVERAGE(--TEXTSPLIT([@Weights]," "))
C2:C5	C2	=COUNT(--TEXTSPLIT([@Weights]," "))

 

[JenniferMurphy]

Another option (probably not better) would be to use a double negative in front of TEXTSPLIT.

Book1
	A	B	C
1	Weights	Average	Count
2	65 64 68 62 69 70	66.33333333	6
3	64 65 64 65 64 65 64 65	64.5	8
4	55	55	1
5	64 65 66	65	3
Sheet4

Cell Formulas
Range	Formula
B2:B5	B2	=AVERAGE(--TEXTSPLIT([@Weights]," "))
C2:C5	C2	=COUNT(--TEXTSPLIT([@Weights]," "))

I've noticed that before. I guess the first "-" converts text to negative numbers and the second converts those to positive. Right?

But why doesn't a "+" sign work?

Mr Excel.xlsx
	B	C	D	E	F
4	Weights	Average	Count	Average2	Average3
5	65 64 68 62 69 70	66.33	6	66.33	#DIV/0!
6	64 65 64 65 64 65 64 65	64.50	8	64.50	#DIV/0!
7	55	55.00	1	55.00	#DIV/0!
8	64 65 66	65.00	3	65.00	#DIV/0!
9	5 3 0 -1	1.75	4	1.75	#DIV/0!
1-Cell Calulations

Cell Formulas
Range	Formula
C5:C9	C5	=AVERAGE(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
D5:D9	D5	=COUNT(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
E5:E9	E5	=AVERAGE(--TEXTSPLIT([@Weights]," "))
F5:F9	F5	=AVERAGE(+TEXTSPLIT([@Weights]," "))

 

[AhoyNC]

Yes you are right about the double negative. You could also multiple by one or add 0.
My guess is that by just putting a + in front of a text number doesn't change its value where as performing a mathematical operation will change it to a value.

Book1
	A	B	C
1	Weights	Average	Count
2	65 64 68 62 69 70	66.33333333	6
3	64 65 64 65 64 65 64 65	64.5	8
4	55	55	1
5	64 65 66	65	3
Sheet4

Cell Formulas
Range	Formula
B2:B5	B2	=AVERAGE(TEXTSPLIT([@Weights]," ")*1)
C2:C5	C2	=COUNT(--TEXTSPLIT([@Weights]," "))

 

[JenniferMurphy]

Here's is a table of the various methods discussed herein. Thanks for all of the comments and suggestions.

Mr Excel.xlsx
	B	C	D	E	F	G	H	I
4	Weights	Count	Sum	Numbervalue Average	-- Average	1* Average	0+ Average	+ Average
5	65 64 68 62 69 70	6	398	66.33	66.33	66.33	66.33	#DIV/0!
6	64 65 64 65 64 65 64 65	8	516	64.50	64.50	64.50	64.50	#DIV/0!
7	55	1	55	55.00	55.00	55.00	55.00	#DIV/0!
8	64 65 66	3	195	65.00	65.00	65.00	65.00	#DIV/0!
9	5 3 0 -1	4	7	1.75	1.75	1.75	1.75	#DIV/0!
1-Cell Calulations

Cell Formulas
Range	Formula
C5:C9	C5	=COUNT(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
D5:D9	D5	=SUM(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
E5:E9	E5	=AVERAGE(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
F5:F9	F5	=AVERAGE(--TEXTSPLIT([@Weights]," "))
G5:G9	G5	=AVERAGE(1*TEXTSPLIT([@Weights]," "))
H5:H9	H5	=AVERAGE(0+TEXTSPLIT([@Weights]," "))
I5:I9	I5	=AVERAGE(+TEXTSPLIT([@Weights]," "))

 

[Alex Blakenburg]

As @AhoyNC indicated Excel seems to ignore any "+" signs that aren't involved in a mathmatical operation.

Book1
	A	B	C	D	E	F	G	H	I	J
1		=+TRIM(2)	=++TRIM(2)	=+++TRIM(2)	=TRIM(2)+0	=-TRIM(2)	=--TRIM(2)	=TRIM(2)*1	=TRIM(2)/1	=TRIM(2)^1
2		2	2	2	2	-2	2	2	2	2
3
4	isNumber -->	FALSE	FALSE	FALSE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE
Sheet2

Cell Formulas
Range	Formula
B1:J1	B1	=FORMULATEXT(B2)
B2	B2	=+TRIM(2)
C2	C2	=++TRIM(2)
D2	D2	=+++TRIM(2)
E2	E2	=TRIM(2)+0
F2	F2	=-TRIM(2)
G2	G2	=--TRIM(2)
H2	H2	=TRIM(2)*1
I2	I2	=TRIM(2)/1
J2	J2	=TRIM(2)^1
B4:J4	B4	=ISNUMBER(B2)

 

[JenniferMurphy]

As @AhoyNC indicated Excel seems to ignore any "+" signs that aren't involved in a mathmatical operation.

Ok. Here's a more complete table.

Mr Excel.xlsx
	B	C	D	E	F	G	H	I	J	K
4	Weights	Count	Sum	Numbervalue Average	-- Average	1* Average	0+ Average	/1 Average	^1 Average	+ Average
5	65 64 68 62 69 70	6	398	66.33	66.33	66.33	66.33	66.33	66.33	#DIV/0!
6	64 65 64 65 64 65 64 65	8	516	64.50	64.50	64.50	64.50	64.50	64.50	#DIV/0!
7	55	1	55	55.00	55.00	55.00	55.00	55.00	55.00	#DIV/0!
8	64 65 66	3	195	65.00	65.00	65.00	65.00	65.00	65.00	#DIV/0!
9	5 3 0 -1	4	7	1.75	1.75	1.75	1.75	1.75	1.75	#DIV/0!
1-Cell Calulations

Cell Formulas
Range	Formula
C5:C9	C5	=COUNT(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
D5:D9	D5	=SUM(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
E5:E9	E5	=AVERAGE(NUMBERVALUE(TEXTSPLIT([@Weights]," ")))
F5:F9	F5	=AVERAGE(--TEXTSPLIT([@Weights]," "))
G5:G9	G5	=AVERAGE(1*TEXTSPLIT([@Weights]," "))
H5:H9	H5	=AVERAGE(0+TEXTSPLIT([@Weights]," "))
I5:I9	I5	=AVERAGE(TEXTSPLIT([@Weights]," ")/1)
J5:J9	J5	=AVERAGE(TEXTSPLIT([@Weights]," ")^1)
K5:K9	K5	=AVERAGE(+TEXTSPLIT([@Weights]," "))