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