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.
You asked: Is it possible to select the correct lookup table by linking to the strength quality and number of reps e.g. SE, 3 , so it would select Strength Endurance list and highlight all the 3 rep schemes.
And the short answer is, "Yes, it's possible." The reality is that I've just in the last day or two been learning how to do that, and am still early on that learning curve.
Excel has introduced, over the last year, a whole new approach to calculating and working with tables--Dynamic Arrays and special Array Functions--and with them it becomes surprisingly easy to do the kind of "magic" that, if it could be done in earlier generations of Excel could only be done with great effort and far less reliably. As I said, though, I'm still on the learning curve. So I'll give this some design time (for my own learning as well as helping you) but it'll have to be later today.
In the meantime, you might enjoy climbing the learning curve yourself. Here's a good place to start. https://www.youtube.com/watch?v=9I9DtFOVPIg
