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 

Josie -- it was I who screwed up. Not you. It has taken me probably 90 minutes to clean up the mess I left it in. I hope it now works as was intended. There ARE a couple of tricky things in here, especially if you've not been exposed to such functions as INDIRECT, or to the newer dynamic array functions.

I've added a lot of comments (text boxes) on what was Sheet2 (now renamed "Tables") that are my attempt to explain what's there. I chose to create an altogether new table that takes each unique combination of reps, regardless of which exercise category it fits into, and use it to do the calculation of total reps on what used to be Sheet 1 (Now "Therapists Screen" or something like that) . My guess is that it could be streamlined a bit more in terms of the formulas, and maybe made a bit easier to maintain.

I'm hoping, though, that your friends won't be making a lot of new combinations. I do think that those odd rep combinations should be cleaned up. Ideally, they'd all be in the form #,#,#,#,   followed by the total of those numbers, and then the count of #s.  Not just something like 20-40.  For those, I'd assume they actually always pick a multiple of 5, so it would be better  (cleaner from a database point of view) to just have individual entries of 20, 25, 30 35, 40 etc. each on its own row, with the coresponding correct numbers so as to be consistent with all the others.

So please accept MY apologies for having left you with such a mess....I hope you didn't lose too much sleep.

[#3]

@mathetes  Thank you for this.  I will go through it carefully to understand what you have done.  I have a conference call with my friends tomorrow so I will have a good understanding of how they want to use this information and what they want it to look like.

Again thanks for your help

JosieL

[#4]

@mathetes Hi.  I have had a long conference call with my friends to confirm exactly what they want from this sheet.  It is so much more long-winded without being able to meet face to face at the moment.  However i have got to an understanding of what they want to do.  It is more complicated than they originally described.  i have set up a draft workbook covering their requirements and i have put explanatory notes on the first sheet explaining the layout they are looking for.  All five sheets need to work in the same way.  There is a salmon coloured text box with some questions from me.  i have not tried to put any calculations/formulae in yet.  I have also spent some time looking into the INDIRECT function which I had not ever used.  I can see how useful that can be.  if you have any time to look at my questions on the sheet, it would be great.  I will attempt to construct what they need then. Thanks

[#5]

@JosieL 

I have a few more questions for you to go over with those friends.

• In particular, why five separate sheets?
• Are there five therapists, each expecting to use their own sheet for a client?
• Are there five clients, and the therapist wants a separate sheet for each?
• Why not a single spreadsheet, to be filled in for any given client on any given day of treatment; it can be printed (saved to PDF for re-printing), and then the same "live spreadsheet" used for another client, again saved to PDF....

In general I have an aversion to creating five duplicate (another word is "redundant") spreadsheets because of the potential for internal inconsistencies to develop (somebody innocently or inadvertently makes a change to one, that gets copied over)... And then there's the maintenance factor: you can be pretty sure that they're going to want further changes....will you do those five times, or just once and copy that sheet yet again, getting rid of the old duplicates?

You've already hit on some of the potential logistical problems of having duplicate sheets--yes, to your final question: they'd all be able to access the same Tables sheet. But if there were to be references going the other way, or if for some reason the user of one of the "front sheets" wanted some unique changes to one of the shared tables.....that could create data integrity problems.

It's not that hard to come up with ways to use a single output sheet--to the extent that's actually what these are: even thought you're also entering "raw data" in them, they're clearly formatted to be able to print or otherwise display for the client--so back to my thought, it might make more sense to have a way to create a (modifiable) exercise plan for each client, a plan that consists of up to 12 individual exercises, create a database of those client-specific exercise regimens, and revise and print them on an as-needed basis. Then this screen, so beautifully designed, becomes mainly a way to show a plan already created?

In that way of thinking, you could easily have just two sheets, and they'd be different. One would be for the creation of an exercise plan; the other would be for the display of that plan. The first would be for the therapist to use in setting up the plan; the latter would be for the client to have (or the therapist to have each day for the clients being seen that day).

Essentially, I'm looking for a more complete description of the (human) work process into which this is going to fit. And I'd try to separate Input from Output if it makes sense; which (in this case) I think it does.

I don't know the extent of your experience with spreadsheet or database design. My own is limited and self-taught. I do have fun getting into it. On another thread here in techcommunity, just last week, there was a link to a series of documents regarding horrible experiences and best practices around spreadsheet design. I clipped one of them that I found a helpful review. Its title is "How Do You Know Your Spreadsheet is Right?" I'm attaching it on the basis of an assumption that you too will find it helpful. A couple of sub-headings stand out:

• Make your spreadsheet as simple as possible, but no simpler.
• Plan to throw one away; you will, anyway. (we may have already done that!)
• Design for change.
• Keep input, logic, and reports separated

There are many more, with rationale for each. It's well worth reading, even if you're very experienced.

[#6]

@mathetes Hi again.  Thanks for your thoughts.  Basically each workbook is for one client. The separate sheets are for logging the workouts as they progress.  The trainer keeps these workbooks to monitor progress, they are not given to the client. The table layout is set out as they require it which I think is fine.   I have asked them if we could put the tables all on one sheet either going down or across so there is just the one active sheet.  Have not had a response yet.

I am also self taught when it comes to spreadsheets.  This is the most challenging one I have had so far because I haven't used arrays before.  I do love spreadsheets though and I am learning such a lot from this one. I am taking up a lot of your time though which I hope isn't a problem.

I will read the clip you have attached.  Thanks

[#7]

@JosieL 

"The table layout is set out as they require it which I think is fine."

It's how they want it. Whether they require it or not is something you (as the professional in the Excel design department) need to determine. Just a finer pint here. Not to beat it to death. There are times when the customer is right; there are times when the customer needs to be educated. In this case they may be right, but there are times when I hope you're comfortable and confident in challenging the incoming design expectations of your clients, doing so graciously, politely. Asking questions to get at the real goal. Often clients (to say nothing of we ourselves) will have a certain expectation because "it's the way we've always done it on paper; I just want Excel to make it look nice."

And there are times--this may be one of them--where Input and Output can be on the same screen, and they may indeed be right, but don't just accept it because they say so.

====================

Arrays--which I hadn't worked with in Excel until the last seven days--have been around for at least half a century. One of the first programming languages I learned was APL, mostly used by mathematicians and scientiests. This was in the early 1970s. In APL you could create multidimensional arrays, well beyond three-dimensions, and when you go beyond 3-D, it's increasingly difficult to even imagine what you're doing, but APL could do that. So Excel's incorporation of Dynamic Arrays is really exciting; it's fun to go back to that.

I'm happy to remain on call, if you want further help. It's helping me get a more complete grasp on the various Array functions. I just spent quite a bit of time over the last day or two updating my own Income and Expense tracking spreadsheet, creating dynamic arrays of the various budget categories so I can add to them dynamically, have the Data Validation lists change dynamically as I realize I need to add to or modify the existing lists. Your project can help me learn in other ways.

[#8]

@mathetes  Hi - yes I get what you are saying about the design of the spreadsheets to meet the needs rather than wants.  However in this case i believe the layout as finalized (they have agreed to the tables all being on one sheet rather than separate sheets) does meet their needs as we discussed on our conference call last week.  It will allow them to track a clients progress through the months and adjust the programme as the individual client needs.  I have attached the sheet so you can see what we have agreed to work with.  My next task is to attach the "Tables" sheet to this so they can pick up the reps schemes as they want. I have left the therapist screen sheet in but i will be deleting that. I have started that today. I have managed to link the data validation lists to all the rows in Sheet 1, workout 1 but I cannot get the reps schemes to link to the lookup table based on the strength quality chosen.  It works in the top row but then doesnt change for subsequent rows.  This means than total reps cannot be calculated based on rep schemes for any other rows.  I have tried to change the formulae to include all the rows and i assume this should be a dynamic array - or should I be using a different function?  It could be that i just wrote the formula incorrectly. I do think that learning by doing is a very good method. Thanks again for your help.  

[#9]

@JosieL 

These INDIRECT references, especially coupled with Dynamic Arrays for the purpose of Data Validation, get very tricky. If we could be confident your therapist friends are going to be adding new combinations, we could make the various tables static, but at this stage of development in particular, I don't think that's realistic.

So here's a trick I employed recently in a spreadsheet of my own, for tracking our household expenses by category....I knew that I'd be adding categories and sub-categories so needed to be able to make sure that new table entries got picked up in the data validation even for altogether new main categories. So I added a hidden column, did the VLOOKUP to get the name of the dynamic array of acceptable values in that hidden helper column, and it works wonderfully.

I've created that in the first of your tables here....and will let you copy it the rest of the way down. The #NA error shows up when the "strength quality" field is blank, but it's a hidden column, so doesn't matter. When you're doing any maintenance, it's a good idea to unhide the column, because that error message actually helps YOU, but I'd always hide it from the user's view.

Let me know of continuing questions.

[#10]

@mathetes  thank you.  I did look at hidden "helper" columns but I couldn't get it right.  It looks really good. 

[#11]

@mathetes Hi again!!!! I have looked at the sheet now and the Rep Scheme column does not change in the rows below the top row. It only links to the number of reps set in the top row.  For example if there is 4 in the Sets of Reps column in the top row then no matter what is selected in the lower rows, you still get the list of 4 reps to select from.  Does this also need a "helper" column to make it work? 

If it is simpler to make the sheet work through all the rows by making it Static how would that be done?

Thanks

[#12]

@JosieL 

I just stumbled across this YouTube video, which presents an even easier way to accomplish those dependent drop downs with dynamic arrays. https://www.youtube.com/watch?v=pvT1ir4kjIM

No use of INDIRECT. Instead, it uses FILTER to create that secondary drop down "on the fly"--I'm quite sure it will address that problem you just posted above this note. See if you can follow it (the video) and apply it.  HOWEVER, that method (in the demo) is used only for one cell; your sheet has multiple rows that need to be based on different criteria. Your situation is a lot more complicated and may need another helper column or something like that.

I'll be making some changes in my own expense tracker to make sure I can accomplish this

[#13]

@JosieL 

Maybe there's a simpler way. Let me suggest, since it's your problem, that you post another question--starting a new thread-- asking the following. I've looked for a solution unsuccessfully or I's tell you the answer.

Is there a function that can take a cell containing something like the following--a series of numbers (but stored as text)--separated by commas, and yield the sum of those numbers (36, in the example given). Not through a series of LEFT, MID, and RIGHT functions, please, because in my application, the series of digits can vary in length, although usually fewer than 10.

8,8,6,6,4,4

[#14 · Accepted solution]

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

[#15]

@Riny_van_Eekelen 

=SUM(IFERROR(--(MID(A1,SEQUENCE(LEN(A1),1,1),1)),0))

I'm sorry I can't give you more than 1 "like"-- thank you thank you thank you. And thank you especially for explaining it, not just providing the solution.   I was pretty sure I'd seen a formula that did this, could work with the array of numbers in a text field. Need to add it to my tool box.

I'm editing this now an hour or two after I first started to respond. My gratitude is still overwhelming and real. I have to add, however, that it turned out when I used that formula there were a few sequences in the tables--VERY FEW--that contained two digit numbers. And when that was the case, the result was inaccurate. It treated a 10 as consisting of 1 and 0 and worth, therefore, 1. Or 12 had a value of 3. And so forth. For the moment, therefore, the spreadsheet that I've revised for @JosieL still uses a clunkier solution--a lookup table that just treats all the strings as text, no matter how bizarre some of it can be.

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.

I think @JosieL  could also just give over the one or two rows at the bottom of each user form, offering the possibility of entering "free form" numbers--in effect doing it "manually"--which is kind of how I read the intent anyway. Eliminate those "fuzzy" figures from the tables altogether. Hopefully we will be able to get her clients to create basic tables that are more computer friendly, although in general that sort of "tail wagging the dog" is less than elegant.

[#16]

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.

I think @JosieL  could also just give over the one or two rows at the bottom of each user form, offering the possibility of entering "free form" numbers--in effect doing it "manually"--which is kind of how I read the intent anyway. Eliminate those "fuzzy" figures from the tables altogether. Hopefully we will be able to get her clients to create basic tables that are more computer friendly, although in general that sort of "tail wagging the dog" is less than elegant.

[#17]

@JosieL 

Josie --- I've worked on this a bit more and am far more confident that this is working as desired. Sadly, that very sleek formula that @Riny_van_Eekelen gave us returned errors for the few exercise sets that had numbers like 10 or 12 in them (they were treated as 1 or 3 respectively). So for the time being, I've continued to use that VLOOKUP function with a lengthy table that takes every odd combination they gave you and just looks it up to get the sum.

In order to make those original data validation (drop down) cells work, I've created a whole bunch of dynamic tables and given them all range names that can be accessed via INDIRECT in the data validation on successive rows of the table. It took me a while to figure out how to give that flexibility, to go to different data  sources on different rows. It was far easier when we just had one entry on our original screen.

Anyway, I've added comments that are intended to help you navigate around the revised Tables tab. And I've only filled in the revised formulas in the first of the multiple entry forms on your first tab. I figured you'd like to work through the remaining ones, tracking how each column is working, and finishing it off.

If you still want my (sometimes totally ineffective) help, I remain available. Sorry for the confusion. I am obviously still in learning mode myself.

[#18]

@mathetes Yeah! My formula doesn't work with 10's and 12's. Sum 1 and 0 and you get 1. Grrr.... Said I didn't go into the details.

Edit: Or could you enter 10 as 5+5. Trying to be creative.

:)

[#19]

@JosieL 

Let me add a postscript to my last message. If you've read through all of this now very long thread, especially my last couple of exchanges with Riny, you'll have noticed that I really would recommend that you EITHER:

• get your clients to "clean up" their odd entries, those few reps that appear as "2-4" or "3-5" which can't really meaningfully or consistently be turned into a sum for the subsequent math, whether by Riny's formula OR by the VLOOKUP function and table.

OR:

• leave the bottom two rows of each entry area for free form (aka "manual") entry of numbers of reps, etc. This would not be all that problematic, I wouldn't think, because my guess at least is that those are infrequently used anyway.

If I were in your place, I'd be strongly pushing for the former to the extent possible. But having gone to a physical therapist a number of times this last year, I can imagine that keeping one or two rows for ad hoc use might still be useful to them.

[#20]

@Riny_van_Eekelen 

Yeah! My formula doesn't work with 10's and 12's. Sum 1 and 0 and you get 1. Grrr.... Said I didn't go into the details.

It did leave me curious as to whether a small change to the formula would enable it to handle two or three digit numbers.

Edit: Or could you enter 10 as 5+5. Trying to be creative.

:)

I thought about that at the time. Sadly, I don't think it meets Josie's clients' needs. If it does, though, it clearly could work.

@JosieL? Something else to ask your clients about.

[#21]

@mathetes Hi.  I have just caught up with all these messages.  I haven't looked at what you have done on the spreadsheet yet.  I will do that next.  I just wanted to say how much I appreciate your help.  This is a very steep learning curve for me so I am very grateful.  With regard to the awkward reps e.g. 2-4 ,15-20 etc. I have spoken to them about this issue this morning because it was something I was thinking about a lot last night.  We have agreed that we can use the largest number in each of those ranges.  However I do think that your idea of having some cells that they can fill manually would be the way forward in reality. They are bound to want to do something that doesnt fit at some point!!

[#22]

@Riny_van_Eekelen Thank you for your idea.  Although it may not help me on this occasion I think that I will find it useful and i will be playing around with it as part of my learning.  I have not worked with arrays before and this has all been really helpful.  Thank you very much.