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.
Hi thanks for that but its not exactly what I am looking for. They want the drop down to show like this
| A | B | C | D | E |
| Movement | Percentage | Rep Sheme | Total sets | Total Reps |
| 5,4,3,2,1 | 5 | E = sum of C * D | ||
| 12 | ||||
| 8 |
Then they want the sum of whichever option is chosen eg. (5+4+3+2+1) multiplied by adjacent cell in column d to give a total in column E. so for example (5+4+3+2+1)*5=75. Can you make a formula that uses the option chosen from a drop down list and then do this calculation? Say the calc was done with one choice, if they select a different option from dropdown would calc update?
I cant work out if this is even possible
Thanks JosieL
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.
Hi Thanks for your reply.
I have been asked to try and come up with this spreadsheet for some friends who are personal trainers. At the moment they put it all together manually for every client - about 200 between them all. Basically each exercise (movement) has a different repetition (rep) scheme depending on what the client./trainer is trying to achieve. There are a large number of repetition schemes but not all are used with every movement. Some schemes have 2 variables, some have 2,3,4,5 or 6. They are in data validation cells.
The movement cell has the name of the exercise. It will not be used in the calculation but will probably be required to be put into a drop down list. The percentage is percentage of maximum volume for the exercise set and also does not have to be included in the calculation.
Thanks for your help
JosieL
