Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Combine FREQUENCY and SUM of Associated Values
I have two columns of data:
Col A1:A200 are Distances (not all cells have values in them) Col B1:B200 are Values associated with each Distance, but some Values are positive and some are Negative I have been successful in calculating the Frequency of the various values within each Distance Bin (col C1:C10) I need two more columns contiguous to the Distance Bin showing: Sum of positive values for each frequency in the Bin Sum of Negative Values for each frequency in the Bin I tried: {=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))} for the positive Column, but get #NA I would greatly appreciate any help and assistance. |
#2
|
|||
|
|||
Hi!
Sumproduct is much easier: For positive values: Enter this formula in D2: =SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200) Copy across to E2 then edit the formula in C2 and change: --($B$1:$B$2000) to --($B$1:$B$200<0) Then select both D2 and E2 and copy down to row 10. Biff "MichaelC" wrote in message ... I have two columns of data: Col A1:A200 are Distances (not all cells have values in them) Col B1:B200 are Values associated with each Distance, but some Values are positive and some are Negative I have been successful in calculating the Frequency of the various values within each Distance Bin (col C1:C10) I need two more columns contiguous to the Distance Bin showing: Sum of positive values for each frequency in the Bin Sum of Negative Values for each frequency in the Bin I tried: {=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))} for the positive Column, but get #NA I would greatly appreciate any help and assistance. |
#3
|
|||
|
|||
THANK YOU Biff! (OK to shout appreciation, right?)
"Biff" wrote: Hi! Sumproduct is much easier: For positive values: Enter this formula in D2: =SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200) Copy across to E2 then edit the formula in C2 and change: --($B$1:$B$2000) to --($B$1:$B$200<0) Then select both D2 and E2 and copy down to row 10. Biff "MichaelC" wrote in message ... I have two columns of data: Col A1:A200 are Distances (not all cells have values in them) Col B1:B200 are Values associated with each Distance, but some Values are positive and some are Negative I have been successful in calculating the Frequency of the various values within each Distance Bin (col C1:C10) I need two more columns contiguous to the Distance Bin showing: Sum of positive values for each frequency in the Bin Sum of Negative Values for each frequency in the Bin I tried: {=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))} for the positive Column, but get #NA I would greatly appreciate any help and assistance. |
#4
|
|||
|
|||
Feedback is always welcome!
There was a typo but it sounds like you figured it out: Copy across to E2 then edit the formula in C2 and change......... Should read: Copy across to E2 then edit the formula in E2 and change......... Biff "MichaelC" wrote in message ... THANK YOU Biff! (OK to shout appreciation, right?) "Biff" wrote: Hi! Sumproduct is much easier: For positive values: Enter this formula in D2: =SUMPRODUCT(--($A$1:$A$200$C1),--($A$1:$A$200<=$C2),--($B$1:$B$2000),$B$1:$B$200) Copy across to E2 then edit the formula in C2 and change: --($B$1:$B$2000) to --($B$1:$B$200<0) Then select both D2 and E2 and copy down to row 10. Biff "MichaelC" wrote in message ... I have two columns of data: Col A1:A200 are Distances (not all cells have values in them) Col B1:B200 are Values associated with each Distance, but some Values are positive and some are Negative I have been successful in calculating the Frequency of the various values within each Distance Bin (col C1:C10) I need two more columns contiguous to the Distance Bin showing: Sum of positive values for each frequency in the Bin Sum of Negative Values for each frequency in the Bin I tried: {=SUM(IF(FREQUENCY(MATCH(A1:A200,C1:C10,1),MATCH(B 1:B200:C1:C10,1))0,""))} for the positive Column, but get #NA I would greatly appreciate any help and assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|