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.
I can think of a couple of ways to do this, but need a better (more complete) description of what "they" have asked for.
- For example, is there a finite set of pre-defined combinations under your "Rep scheme" heading?
- Among those schemes, is there a constant number of variables (e.g., always 5 numbers, possibly some with more digits, some with fewer, but no more than 5 numbers) or will it sometimes be 3 numbers, sometimes 4, sometimes 5, etc?
- And is there a finite set of pre-defined numbers under your "Total Sets" column?
- What are the "Movement" and "Percentage" columns about? What information do they contain? Are they also drop-downs (data validation cells), and do they factor into this in any way? If not, why are they there?
And if I could be so bold: WHAT is this all about anyway? That is, what's the larger context here? Who are "they"?
Hi Further to my reply on Saturday, I have spoken to my friends to get more information on the numbers of rep schemes. There are a large number of variations and the choices are related to what the required outcome of the training is. I have attached a screenshot she has sent me showing the full list. I was wondering if by adding an additional column to show what the training was designed to achieve e.g. relative strength, functional hypertrophy, hypertrophy or strength endurance as per the screenshot, this could make it easier to select the appropriate rep scheme and therefore the calculation as specified in previous communication. Does this help or not, thanks
Here's a start --- the heart would be taking all those figures on the page you've shown and creating from them a huge table that could be used as a reference in a VLOOKUP formula. There may be easier ways to do it, but this has the advantage of being very visible to the user...so if "they" wanted to add a new RepScheme, for example, they could do so. This table is defined as an official Excel Table, so as you add rows, it grows and new rows are automatically included in the VLOOKUP's references.
The data validation list (as designed so far) will need to be extended as you add new rows. There no doubt is a way for that to grow as the Table grows, but I haven't figured it out yet and wanted to get this to you to start.
I do wonder, however, whether those percentages need to be factored in. They could certainly also be retrieved by a VLOOKUP formula... But that's where we need a more complete description of what you're trying to do.
mathetes Hi. Thank you for this. 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? I cant quite get my head around the revision that
https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176&data=02%7C01%7C%7C77712a81ce72478500fc08d7dfc3d6ae%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637223904531733846&sdata=lFG7DjOIS5gf5vybwjdpzNit0JxMsXr9nnyLcLutWto%3D&reserved=0
sent. I have asked again for a check about the percentages but everything I have been told indicates that they are not required for the calcs.
This is very exciting - I am learning so much more about Excel. I will make the table up
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.....
mathetes Not sure I follow everything but I've added a small thing to the second sheet that allow for the drop-down to expand automatically with an expanded table. It's the magic #
I wondered if it was that piece of magic dust. Thanks! I continue to learn the larger magic of the new Table system.
But I see you added, in essence, a separate "helper column" to do this. Is it not possible to just make the Data Validation look at the first column of the basic table, and expand with it?