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
