Forum Discussion
Help with data calculations
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.
Josie -- it was I who screwed up. Not you. It has taken me probably 90 minutes to clean up the mess I left it in. I hope it now works as was intended. There ARE a couple of tricky things in here, especially if you've not been exposed to such functions as INDIRECT, or to the newer dynamic array functions.
I've added a lot of comments (text boxes) on what was Sheet2 (now renamed "Tables") that are my attempt to explain what's there. I chose to create an altogether new table that takes each unique combination of reps, regardless of which exercise category it fits into, and use it to do the calculation of total reps on what used to be Sheet 1 (Now "Therapists Screen" or something like that) . My guess is that it could be streamlined a bit more in terms of the formulas, and maybe made a bit easier to maintain.
I'm hoping, though, that your friends won't be making a lot of new combinations. I do think that those odd rep combinations should be cleaned up. Ideally, they'd all be in the form #,#,#,#, followed by the total of those numbers, and then the count of #s. Not just something like 20-40. For those, I'd assume they actually always pick a multiple of 5, so it would be better (cleaner from a database point of view) to just have individual entries of 20, 25, 30 35, 40 etc. each on its own row, with the coresponding correct numbers so as to be consistent with all the others.
So please accept MY apologies for having left you with such a mess....I hope you didn't lose too much sleep.
