+ Reply to Thread
Results 1 to 6 of 6

Calculate series of numbers with different values

Hybrid View

  1. #1
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    Calculate series of numbers with different values

    Hello,
    I would thank you if you could help me. I have the next problem:

    A list of numbers from 1 (column A), with values ( column B) that indicates the number of times that repeats the number of column A:
    1 2(times)
    2 0
    3 1
    4 0
    5 3
    6 2
    7 0
    8 0
    9 2
    10 1
    11 0
    12 2
    13 1
    14 5
    15 0
    16 1
    17 2
    ...

    What I want is, in the next column (C), find the result of this operation:
    I take 12 numbers in order: from 1 to 12, from 2 to 13, from 3 to 14, etc.
    With every 12 numbers I make the next:
    The first number sum 11, the second 10, the third 9, the forth 8, the fifth 7, the sixth 6, the seventh 5, the eighth 4, the ninth 3, the tenth 2, the eleventh 1, and the twelfth 0. But the numbers that cames after subtract 12, and those that cames behind values 0.

    Then I want to calculate the result of the value of the number of Column A multiply for the number in the column B

    The values will be like this, if we start from 1 (This is not the result I'm searching, but just for explain the values of every row):

    1 2 +11*2 = +22
    2 0 +10*0 = 0
    3 1 +9*1 = +9
    4 0 +8*0 =0
    5 3 +7*3=+21
    6 2 +6*2=+12
    7 0 +5*0=0
    8 0 +4*0=0
    9 2 +3*2=+6
    10 1 +2*1=2
    11 0 +1*0=0
    12 2 +0*2=0
    13 1 -12
    14 5 -12
    15 0 -12
    16 1 -12
    17 2 -12

    What I'm looking is the result of this values:
    +22+9+21+12+6+2-12-12-12-12-12= +12
    This is the result of the first row.

    The second Row will be like this:
    Values:
    1 2 0
    2 0 +11*0
    3 1 +10*1
    4 0 9*0
    5 3 8*3
    6 2 7*2
    7 0 6*0
    8 0 5*0
    9 2 4*2
    10 1 3*1
    11 0 2*0
    12 2 1*2
    13 1 0*1
    14 5 -12
    15 0 -12
    16 1 -12
    17 2 -12

    We can see here that row 1 have now a value of 0, because we left behind. The result will be:
    +10+24+14+8+3+2-12-12-12-12=+13

    Then the result of the second row (the result of start to calculate from this second row) is 13

    Then the results will be like this:
    1 2 +12
    2 0 +13
    3 1 ...
    4 0
    5 3
    6 2
    7 0
    8 0
    9 2
    10 1
    11 0
    12 2
    13 1
    14 5
    15 0
    16 1
    17 2 +22

    And with the formula we could find the rest of results.
    When we arrive to the last row, in this case 17, the calculation will be the next:
    All the values behind will be 0, and 17 values 11*2=22

    Thank you very much for your help,
    Regards,
    Caldera

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Assuming that A2:B18 contains the data, try...

    C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =INDEX(MMULT(TRANSPOSE(ROW($B$2:$B$18)^0),IF((ROW($A$2:$A$18)-ROW($A$2)+1>=1+TRANSPOSE(ROW($A$2:$A$18)-ROW($A$2)))*(ROW($A$2:$A$18)-ROW($A$2)+1<=12+TRANSPOSE(ROW($A$2:$A$18)-ROW($A$2))),($B$2:$B$18)*(12-(ROW($A$2:$A$18)-ROW($A$2)+1)+TRANSPOSE(ROW($A$2:$A$18)-ROW($A$2))),IF(ROW($A$2:$A$18)-ROW($A$2)+1<TRANSPOSE(ROW($A$2:$A$18)-ROW($A$2)+1),0,-12))),ROWS(C$2:C2))

    Hope this helps!

  3. #3
    Registered User
    Join Date
    05-15-2008
    Posts
    38
    Hello Domenic,
    Thank you very much again. It's a great job.
    I think I don't explain well one detail, because trying to synthesize.
    In the results I want to substract 12 each time it is at least 1 in the column B, but not in the times it is no value, not in the time it is 0.
    I think it's easier to see with the example. I attached the worksheet I'm using.

    If we just analyze the last numbers:

    Row Number Time Results Results I'm looking for
    38 37 -148 -16 (=+8(row41)-12(row49)-12(row62))
    39 38 -135 -3 (=+9(row41)+0(row49)-12(row2 62) )
    40 39 -121 -1 (=+10(row 41)+1(row49)-12(row62))
    41 40 1 -107 1 (=+11(this row)+2(row50)-12 (row 62))
    42 41 -105 -9
    43 42 -92 -8
    44 43 -79 -7
    45 44 -66 -6
    46 45 -53 -5
    47 46 -40 -4
    48 47 -27 -3
    49 48 -14 -2 (=+10 (row50) -12 (row 62))
    50 49 1 -1 (=+11 (this row)-12 (row 62))
    51 50 0
    52 51 1
    53 52 2
    54 53 3
    55 54 4
    56 55 5
    57 56 6
    58 57 7
    59 58 8
    60 59 9
    61 60 10
    62 61 1 11


    We can see that in the row 49 (number 48) the result with the formula is -14, that is the result of
    +10 (the value of the next cell-cell50) and subtract the cell 61 and 62, but I just look for subtract one time 12, that is wich have the 1 in column C.
    The results from row 50 to 62 are what I'm looking for.

    I'm sorry for the confusion.
    Thank you very much,
    Caldera
    Attached Files Attached Files
    Last edited by caldera55; 05-18-2008 at 11:14 AM.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    C2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

    =SUM(IF(ROW($B2:$B$62)-ROW($B2)+1<=12,($B2:$B$62)*(12-(ROW($B2:$B$62)-ROW($B2)+1)),IF($B2:$B$62>0,-12)))

    Hope this helps!

  5. #5
    Registered User
    Join Date
    05-15-2008
    Posts
    38

    Thank you very very much

    Thank you very much, Domenic.

    It's great.
    I don't know how I could to give you thanks.
    I made another mistake in my explanation, but I did found how to solve the problem:
    it was that in the -12 rows there is more value than one, it has to multiply for that number. I analyze the formula, and then I add this:

    =SUM(IF(ROW($B2:$B$62)-ROW($B2)+1<=12,($B2:$B$62)*
    (12-(ROW($B2:$B$62)-ROW($B2)+1)),IF($B2:$B$62>0,-12*$B2:$B$62)))

    In spanish:
    =SUMA(SI(FILA($B2:$B$62)-FILA($B2)+1<=12;($B2:$B$62)*
    (12-(FILA($B2:$B$62)-FILA($B2)+1));SI($B2:$B$62>0;-12*$B2:$B$62)))

    It's just a detail, but it makes me very happy, that works so fine.

    I give you a present, that is too a present for everybody that had help me. It's a photograph I made, that I like so much, and give me good feeling, and calmness.
    Attached Images Attached Images

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    You're very welcome! And thanks for the feedback! By the way, nice photograph!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1