Forum Discussion

JosieL's avatar
JosieL
Brass Contributor
Apr 10, 2020
Solved

Help with data calculations

Hi I have been asked to set up a spreadsheet with a dropdown box containing various options such as 5,4,3,2,1; 10,10,10,8,8 and various other ones.  They want to select an option from the dropdown bo...
excel
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Apr 27, 2020

    mathetes 

    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.

     

     

mathetes's avatar
mathetes
Gold Contributor
Apr 11, 2020

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"?

JosieL's avatar
JosieL
Brass Contributor
Apr 11, 2020

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

 

Resources