Results 1 to 1 of 1

Thread: example: sum of*a list of*numbers*recorded*in a single*cell

  1. #1
    VBAX Newbie
    Feb 2011
    Ekaterinburg, RF

    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, thread "Усовершенствование формул. Избушка формУлистов"; translated by google
    Last edited by dl@ru; 02-02-2011 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