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