Help with data calculations

[JosieL]

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

[#2]

@JosieL  Please see attached spreadsheet, hopefully this helps

[#3]

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

[#4]

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

[#5]

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

[#6]

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

[#7]

@JosieL 

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.

[#8]

@mathetes Not sure I follow everything but I've added a small thing to the second sheet that allow for the drop-down to expand automatically with an expanded table. It's the magic #

[#9]

@Riny_van_Eekelen 

I wondered if it was that piece of magic dust. Thanks! I continue to learn the larger magic of the new Table system.

But I see you added, in essence, a separate "helper column" to do this. Is it not possible to just make the Data Validation look at the first column of the basic table, and expand with it?

[#10]

@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 

@Riny_van_Eekelen

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

[#11]

@JosieL 

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.....

[#12]

@mathetes Thank you I will have a look at this now.

I will be back in touch I am sure.  I really appreciate your help

[#13]

@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.

[#14]

@JosieL 

You asked: 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. 

And the short answer is, "Yes, it's possible." The reality is that I've just in the last day or two been learning how to do that, and am still early on that learning curve.

Excel has introduced, over the last year, a whole new approach to calculating and working with tables--Dynamic Arrays and special Array Functions--and with them it becomes surprisingly easy to do the kind of "magic" that, if it could be done in earlier generations of Excel could only be done with great effort and far less reliably. As I said, though, I'm still on the learning curve. So I'll give this some design time (for my own learning as well as helping you) but it'll have to be later today.

In the meantime, you might enjoy climbing the learning curve yourself. Here's a good place to start. https://www.youtube.com/watch?v=9I9DtFOVPIg

[#15]

@JosieL 

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

1. 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)
2. add a column with the count of reps in the scheme
3. 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
4. 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.

[#16]

@mathetes Thank you I will definately watch this. I need to learn.

Thank you  

[#17]

@mathetes Hi this is looking good.  I haven't quite got the full understanding of what you have done especially about the data validation but I will have a play around to see if I can work it out.  My query with the validation is that it only refers to one cell reference but I will work it out.

Thank you

[#18]

@JosieL 

If you read the messages from yesterday with Riny, you'll see his reference to the magic "#" sign...at the end of the data validation cell reference. That means "it spills" to more than one cell, however far it goes.

This is part of the magic of ARRAYs as Excel now implements them. And that's what that video is all about.

[#19]

@mathetes thank you - thats what I had missed.  It makes sense now. Thanks

[#20]

@mathetes Hi I am sorry to bother you again but I am definitely missing something from your instructions.  I have set up the other columns to match what you had done with the first one i.e. Relative Strength but when I try to make a matching formula following what you had done in Sheet 2, column F I get an error message.  i really cant see where I am going wrong but I obviously am!!! If you could add any pointers i would be really grateful. Thank you