
example: sum of*a list of*numbers*recorded*in a single*cell
The problem is the following:
In a single cell (A1) one can find a list of integers, separated by a comma and a space. Numbers can be either positive or negative.
For example: 5, 1, 180, 120, 98, 626, 707, 5000, 20, 14
The sum of all these numbers should be calculated by one formula in one cell.
Solution is found by using the following array formula:
{=SUM((TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",255)),1+(ROW(A1:A999)1)*255,255)) & "0")/10)}
How it work:
SUBSTITUTE(A1,",",REPT(" ",255))
replaces the separator (comma) by 255 spaces, so that each number in the list is inside a sufficiently large empty space.
After that, from the resulting line cut sections containing within itself one number. As it turns out the fact that the length of the number (<16) is much smaller than the length of gap (255 spaces).
TRIM(MID(... ,1+(ROW(A1:A999)1)*255,255))
Whole line is cut to 999 pieces, 255 characters length each, it is done in order not to assume the full amount of numbers in the list. MID function for the initial position more than the length of the string return an empty symbol "".
In carrying out above the string representing the number one of mathematical operations, the string is automatically converted to a number. However, this is not true for blank lines. So " 2 " +0 =2, but ""+0 is error.
To avoid this, each string is concatenated with a zero (& "0"), then the returned strings are divided by 10.
After this it remains only to sum up the resulting number. All generally works well.

From russian forum planetaexcel.ru, thread "Усовершенствование формул. Избушка формУлистов"; translated by google
Last edited by dl@ru; 02022011 at 05:59 AM.
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules