Square each value of a comma seperated string ( of a single cell ) and Get the S

Question

Square each value of a comma seperated string ( of a single cell ) and Get the Sum of all Calculated Values.

I have a formula to do this however, I need to be able to enter comma seperated values into the cell and have the formula use those values in the calculation.

Ill Show you wat I have so far;

Jets =    13,13,13,13,13,13,13,13,13,13    TFA
(Enter up to ten Jets using commas between them.)

Explanation:

The Input Cell is D39,

The Output (TFA) is cell E39, & the formula is

=IF(D39>0,SUMSQ(13,13,13,13,13,13,13,13,13,13,)/1303.8)

Where 1303.8 is a constant the total is divided by.

I am looking for a modification allowing the input values to populate the formula above.

Using 10 (13)'s should produce the value of 1.296.

Please help

Explanation / Answer

I can do it with VB code. ALT+F11 to open vb editor, right click 'ThisWorkBook' and insert module and paste the code below in. Close VB editor.

Back on the worksheet call with

=MySumSq(D39)

Function MySumSq(str As String)
Dim v As Variant, x As Long
Dim temp As Double
v = Split(str, ",")
For x = 0 To UBound(v)
temp = temp + v(x) ^ 2
Next
MySumSq = temp / 1303.8
End Function

or

Try this array formula**:

=IF(D39>0,SUMSQ(0+TRIM(MID(SUBSTITUTE(D39,",",REPT(" ",LEN(D39))),LEN(D39)*(ROW(INDIRECT("1:"&1+LEN(D39)-LEN(SUBSTITUTE(D39,",",""))))-1)+1,LEN(D39))))/1303.8)

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.