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: If I understand you correctly I need to make a table on sheet 2 of the workbook you sent me to include all the rep schemes on that screenshot i was given. Is that correct?
You need to make a table, yes.
Does it need to be on sheet 2? No.
I personally make it a practice to put such things on separate sheets because they're used "behind the scenes" so your users don't need to even see tables like that, which would tend to clutter up the screen where they enter their variables and see the results.
I had used the first column of the Excel Table as the source list for the Data Validation prompts. But the way I did it didn't provide automatically for added rows--and you'll be adding quite a few. What @Riny_van_Eekelen added was a helper column that automatically extends as the basic table extends, so that your data validation (drop down prompts) will lengthen.
The concern there is going to be that the table you showed me (from your therapist friend) has SO many different combinations that it might become impractical. And that may lead to another solution altogether, and I've created that for you in the attached. There's an added sheet in from of the other two, so you have them both. This new one is called "Simpler"
Instead of using data validation (with its associated drop down box) maybe you should have six columns [if the max number of reps in any one scheme is six], allow only single digit entries, so that the user doesn't select from a drop down, but rather enters 7 then 4 then 2 then ....
I've added a comment as to how it could be used.....
