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.
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.
mathetes thank you. I did look at hidden "helper" columns but I couldn't get it right. It looks really good.
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.
=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.
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.
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