Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    30

    Unanswered: Sum of values in frequency's

    Hello,
    I'm trying to sum values that are part of a frequency.
    For example, in column A I have values that are either 0, or any other number. I would like Excel to sum values that are part of a frequency, for example, a set of 2 values in consecutive cells that are not zero. If there are 3 consecutive values its a different frequency (frequency of 3). Frequencies are separated with zero's.

    Example: here is a set of values in column A, from A1:A17
    0
    0
    1
    2
    0
    3
    4
    0
    1
    3
    5
    0
    5
    6
    0
    1
    0
    There are 3 sets of 2 consecutive cells.
    In this case, the formula's result should be 21 (the sum of all the values that are in the set of 2 consecutive cells).

    I know how to make excel count the number of sets, but i cant make it sum the values.
    Any help will be great!
    Thanks a lot!

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    For your example...

    Firstly, insert a header row at the top. Give the column A header a 0 marker to start, so your sequence looks like:
    0
    0
    0
    1
    2
    etc...

    In B2 add in this formula and fill it down the column:
    =(A3>0)*(A4=0)*(A1=0)

    Then the formula to sum all pairs of numbers greater than zero is:
    =SUMIF(B2:B18,1,A2:A18)+SUMIF(B2:B18,1,A3:A19)


    Hope that helps...
    Last edited by Colin Legg; 03-29-10 at 05:04.

  3. #3
    Join Date
    Mar 2010
    Posts
    30
    Hi Colin,
    Thanks for your help again!
    Your formula works, but I forgot to mention a few things (sorry, I’m new to forums...).
    I have a sheet with thousands of row's of values. Each row can either have a value of zero, or any other number.
    The first step that I did was to count the number of times that consecutive cells appear. The count is made for any number of frequency (2 consecutive cells, 3 consecutive cells… etc.)
    The formula I used is:

    =COUNT(IF(FREQUENCY(IF($A$2:$A$31<>0,ROW($A$2:$A$3 1)),IF($A$2:$A$31=0,ROW($A$2:$A$31)))=2,2))

    This formula counts how many times I have 2 consecutive cells with values that are not zero.
    What I need to do now is SUM the values that are part of each frequency. For example, SUM of all the values that combine all the 2 consecutive cells in the give range.

    I’ve attached an example file.

    Thanks again for your help with this!!!
    Roee.
    Attached Thumbnails Attached Thumbnails example.bmp  

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,



    I've been a keen advocate of array formulae over the years, but they have a few drawbacks:
    • They are slow to calculate.
    • They are difficult to understand (and therefore difficult to maintain).
    • They are hard to problem-shoot.
    I'm not saying that you should necessarily avoid a complex array formula to get your sum, but at least consider the following alternative which uses a couple of helper columns and a pivot table.

    In B2 (the 'grouping' column), this formula copied down:
    =IF(A2,SUM(B1,1),)

    In C2 (the 'count' column), this formula copied down:
    =IF(B2*B3,C3,B2)

    Finally, to get a weighted count (to derive the count of each frequency):
    =IF(C2,1/C2,0)

    Then it's just a case of summarising the count and sums for all frequencies in a simple pivot table. I think this is a fairly straightforward solution.

    Hope that helps...
    Attached Files Attached Files
    Last edited by Colin Legg; 03-30-10 at 06:40.

  5. #5
    Join Date
    Mar 2010
    Posts
    30
    Colin,
    Thanks a lot!!! Its working!
    Just out of couriosity, how would the array formula look for something like that? I tried replacing the COUNT at the begining with SUM, thinking that it'll work...

    Thanks again for your great help!

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    Just out of couriosity, how would the array formula look for something like that? I tried replacing the COUNT at the begining with SUM, thinking that it'll work...
    This comes back to the point I made earlier that these formulas are pretty tough to understand!


    Taking the attachment in post #4 as our example, for a 'frequency' of 2 the count and sum in H8 and H9 (in the pivotable) are 288 and 3966.

    To get the total count of 288, a variation of your FREQUENCY() formula:
    Code:
    {=SUM(--(FREQUENCY(
        IF($A$2:$A$2161<>0,ROW($A$2:$A$2161)),
            IF($A$2:$A$2161=0,ROW($A$2:$A$2161)))=2))}
    To get the total sum of 3966, in XL 2007 the best I could do was along the lines of:
    Code:
    {=SUM(IFERROR(N(OFFSET(A2,(IF(FREQUENCY(
        IF($A$2:$A$2161<>0,ROW($A$2:$A$2161)),IF($A$2:$A$2161=0,ROW($A$2:$A$2161),0))
            =2,(ROW($A$2:$A$2161)-{1,2}),""))-ROW(A2),0,{1,2},1)),0))}
    It's pretty gnarly stuff - volatile, slow, complicated. If you're using a version of XL prior to 2007, the SUM(IFERROR(... part of the formula would have to be changed to SUM(IF(ISERROR(... and would basically end up twice as long.

    I confess I'm not the best at these FREQUENCY() formulas, so it might well be that it can be improved upon.

    Revised (.xlsx)workbook attached with both the pivot table and formula solutions.


    Hope that helps...
    Attached Files Attached Files
    Last edited by Colin Legg; 03-31-10 at 00:22.

  7. #7
    Join Date
    Mar 2010
    Posts
    30
    Hi,
    Wow, it is complicated.
    Thanks for all your help.
    I'm using the other formulas that you suggested and its working great.
    Have a good one!
    Roee.

Posting Permissions

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