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 box and then use the sum of these numbers and multiply them with a number in another cell to give a total number in a third cell.  Is this possible?

 

Thanks in advance for any help, advice or clarification

JosieL

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.

     

     

44 Replies

Sort By
    • JosieL's avatar
      JosieL
      Brass Contributor
      Apr 11, 2020

       

      Hi thanks for that but its not exactly what I am looking for.  They want the drop down to show like this

       
       
       
      ABCDE
      MovementPercentage Rep ShemeTotal sets Total Reps
        5,4,3,2,15E = 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

      • mathetes's avatar
        mathetes
        Silver 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"?

Resources