Help with data calculations

mathetes 

Have been following this thread, without really digging in to the detailed issues. Your latest post triggered me to offer a formula that answers the very specific question. Assume that the string of numbers "8,8,6,6,4,4" sits in A1, the following formula will return 36.

=SUM(IFERROR(--(MID(A1,SEQUENCE(LEN(A1),1,1),1)),0))

Reading this from the inside out:

1) Create and array of numbers based on the length of the text string (SEQUENCE);

2) Separate the text string into its individual characters;

3) "Translate" each character into its number value, using "--". This will result in #VALUE! for the commas and real numbers for the numbers that were stored as texts;

4) If step 3 results in an error, then return zero. Now, you have an array of 11 real numbers like

{8, 0, 8, 0, 6, 0, 6, 0, 4, 0, 4}

......that can easily be summed with SUM.

Now, I do remember reading that there also may be text strings like "5-7" representing 5 or 6 or 7 reps. The above formula will then return 12, which obviously is not the desired answer. Perhaps a coding with only one number can be used. Like "n5" could be used to indicate a minimum of 5, but you may do two more. Or "x7" to indicate a maximum of 7, but you may do two less. Or "≈6" which could mean 6 plus or minus 1. These will then add up to 5, 7 or 6, respectively using the above formula.