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.
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.
mathetes Riny_van_Eekelen I spent yesterday evening going through the changes to the spreadsheet and filling in the other required cells. It seems to work exactly as they want it too. I will hopefully send them the completed worksheet today for feedback -Fingers crossed. Thanks for all the help. Hopefully later I will be giving you a final thanks. I have learnt so much going through this
mathetes I am back again!!! I hope you won't mind if I ask for some further advice regarding this sheet. I have made some changes to the original sheet and it has been in use by my friends and they have found it extremely useful. However they have asked for a change which has meant I need to expand the "salmon coloured" table on the Tables tab to include another column. When I do this I cannot get the indirect data validation to work if that column is selected. The column I have added is Column AV on the tables tab. On the Programmes tab when 96 is selected in Column F the appropriate rep schemes do not show up in the data validation list - cell highlighted yellow on the attached. I assume that I have not made the indirect link. I have spent hours looking at You tube videos and other tutorials but I cannot see how to expand the columns in a table and make it work with the indirect function. I really cannot get my head around this function and would be extremely grateful for any clarification
Hi,JosieL -- it's good to hear from you again.
I'm sorry, but not too surprised that you're finding it difficult to wrap your mind around those "back roads" of making dynamic references to dynamic ranges as at the heart of the drop-down boxes. It's all kind of abstract and magical.
There are a couple of things that make it work:
- Named Ranges first of all
- And then combining text strings (Concatenating, by means of the "&" ampersand) to form the name of one of those "Named Ranges" dynamically, so that INDIRECT refers the data validation (Drop down) to the range that is needed.
- And at "the bottom" in some ways is the creation of the dynamic array of choices that are "valid".
So, starting at that bottom, if you want to make the lists "static" instead of dynamic, what you'll need to do is copy the values in each existing range and use "Paste Special....Values" to paste the values over what are now the "spilled" results of the dynamic array formula in the first row of each of those sets.
Then change the range that carries the Name shown at the top, so that Named Range now refers to the entire range rather than just to the top cell. For example, in your current sheet (unless you've changed it) the Named Range "RelStren" refers solely to cell $J$6. Cell $J$6 contains the formula =I6#, which "spills" the values in I6 through I10 (dynamically generated there too) into J6 through J10. What you want to do is:
- Copy the values from J6:J10 and paste them into the same places using Paste Special....Values. This will eliminate the dynamic array aspect of the older contents.
- The re-define RelStren such that it refers to $J$6:$J$10
- And do the same with ALL of the named ranges all the way across that "Tables" tab in row 5, redefining each of them so that they refer to the entire range of values that are beneath them.
I'd suggest that before you do any of this you make a back-up copy of your spreadsheet so that you can always go back and start over in case things go wrong.
By the way, the INDIRECT in the Data Validation screen will still continue to be used (contrary to what I said a couple messages ago), because it's what directs data validation to the correct range. It's just that the magic that changes the values that appear will be gone.
I think that's it, but just in case...check your private messages.
mathetes Hi Sorry to bother you again. I have spent my free time going through the spreadsheet carefully and I have been able to replicate what you did and understand it. The only thing I cant get my head around is how the reps scheme cell in column G connects to the correct array table. Think I have brain fog from being in lockdown too long!! I want to make the data validations static because there are no other schemes that will be added but because i dont understand how column G works, I cant seem to do it. If you could point me in the right direction I would be so grateful. Thanks
You're certainly very welcome. It's been a learning for me too. The ability to create a set of ad-hoc-extendable dynamic arrays that become "the list(s)" for a dropdown/data validation processes in multiple cells is remarkable. Learning it here (thank you @Riny_van_Eekelen), I've already employed it extensively in my own Income & Expense tracking spreadsheet. As with any other powerful capability, it requires careful handling so as not to get out of control.
You'd asked at one point in this thread, JosieL , about what those data validation tables would look like, and how they'd work, were they to be just static rather than dynamic. And the simple answer is that they'd look very much as they do now. They'd just no longer be dynamic; the user could not add new combinations. And you'd not need to use the INDIRECT in the data validation process; instead it would be to a set of named ranges and those names would refer to the entire range, not using the "#" suffix.