singleton2787
asked on
YEARFRAC question part duex
I have the results of this formula =YEARFRAC(I683,K683,1)*12* SIGN(I683- K683)
and the results are like 1, -2, -6, 4.
What I need to do is:
Count the occurence of each number i.e. there are 40 4s, 29 -4s, etc.
Sum the total of the number of negative numbers and positive numbers (102 positive #s, 68 negatives.
and the results are like 1, -2, -6, 4.
What I need to do is:
Count the occurence of each number i.e. there are 40 4s, 29 -4s, etc.
Sum the total of the number of negative numbers and positive numbers (102 positive #s, 68 negatives.
Here's one way to do it.....
Assume you have the YEARFRAC results in A2:A100 then in C2 use this formula
=MIN(A$2:A$100)
and then in C3 copied down as far as you need and further
=IF(C2="","",IFERROR(SMALL (A$2:A$100 ,COUNTIF(A $2:A$100," <="&C2)+1) ,""))
Column C is now a list of all the [different] results returned by YEARFRAC, in ascending order
Now to get the count of each use this formula in D2 copied down
=IF(C2="","",COUNTIF(A$2:A $100,C2))
see attached where I have generated random numbers in A2:A100. Hit F9 key to re-generate. Change ranges as required for your setup......
I also use COUNTIF formulas as suggested above to give the total of positive, negative and zeroes....
regards, barry
26892508.xlsx
Assume you have the YEARFRAC results in A2:A100 then in C2 use this formula
=MIN(A$2:A$100)
and then in C3 copied down as far as you need and further
=IF(C2="","",IFERROR(SMALL
Column C is now a list of all the [different] results returned by YEARFRAC, in ascending order
Now to get the count of each use this formula in D2 copied down
=IF(C2="","",COUNTIF(A$2:A
see attached where I have generated random numbers in A2:A100. Hit F9 key to re-generate. Change ranges as required for your setup......
I also use COUNTIF formulas as suggested above to give the total of positive, negative and zeroes....
regards, barry
26892508.xlsx
ASKER
Mr. Houdini, I am getting some odd results....I've uploaded the spreadsheet, can you see what I am doing wrong?
Also, I keep thinking a pivot table would do the same thing? Just curious, no big deal.
Book1.xlsx
Also, I keep thinking a pivot table would do the same thing? Just curious, no big deal.
Book1.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Once again...he's the man!
You can count negative numbers in a range with
=COUNTIF(A:A,"<0")
and similarly positive numbers with
=COUNTIF(A:A,">0")
regards, barry