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