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.
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 Hi. I have looked at the "Simpler" idea you had but it doesnt really work for what they want. They wont be adding any other rep schemes. I have typed in all the rep schemes on the screenshot I was given. Because there are so many, I had a thought about how to locate the one they want more easily. I have divided the rep schemes into the same columns as provided in the screenshot i.e. Relative strength, functional hypertrophy, Hypertrophy, strength endurance. These are known as strength quality. I have added a column in the table on sheet one called strength quality and put in a data validation list naming these options. I have also put in a column to identify the number of reps e.g. 3 (5,3,3) etc. 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. I couldnt work out what to do with the ones that said things like 3-5 which would basically be one set of between 3 to 5 reps so I have typed them in as the highest number in the option i.e. in this example 5. I have attached spreadsheet to show what I have done.
I went back to the sheet that @Riny_van_Eekelen had contributed in this thread only yesterday to find the solution.
Here it is. I reworked only the first table for you, but you should be able to follow the pattern.
Essentially it involved these steps
- give the table itself the name that you'd used as a label above each of them (not necessary, but it makes the formulas more naturally readable)
- add a column with the count of reps in the scheme
- use FILTER function to filter the full array so as to meet the criteria, and place those results off to the right of the full table
- revise the Data Validation so it refers to that SPILLed subset of RepSchemes specified; be sure to include the cell reference followed by the # sign
I also changed the front (user-oriented) page to be more user-friendly. And made it such that it just deals with one exercise at a time. This may not be what your users want, but I thought it looked nicer...it would not be hard to have it designed so as to give multiple rows. Really depends on how they'll be using it.