Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

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.
0
singleton2787
Asked:
singleton2787
  • 3
  • 2
1 Solution
 
barry houdiniCommented:
Is the first just a step to calculate the second?

You can count negative numbers in a range with

=COUNTIF(A:A,"<0")

and similarly positive numbers with

=COUNTIF(A:A,">0")

regards, barry
0
 
barry houdiniCommented:
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
0
 
singleton2787Author Commented:
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
0
 
barry houdiniCommented:
What's odd? I see you have some high negative numbers caused by dates in column A in 2099....but the count of numbers in column D is correct as far as I can see. Perhaps you need to suppress those 2099 dates.....or make YEARFRAC ignore those

Perhaps change D2 formula to the following copied down

=IF(ISNA(A2),"",IF(YEAR(A2)>2098,"",YEARFRAC(A2,B2,1)*12*SIGN(B2-A2)))

That will eliminate #N/A and the 2099 dates and then your start number in the distinct numbers range will be -9

regards, barry
0
 
singleton2787Author Commented:
Once again...he's the man!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now