Solved

YEARFRAC question part duex

Posted on 2011-03-16
5
452 Views
Last Modified: 2012-05-11
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
Comment
Question by:singleton2787
  • 3
  • 2
5 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 35153008
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 35153048
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
 

Author Comment

by:singleton2787
ID: 35156431
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 35160272
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
 

Author Closing Comment

by:singleton2787
ID: 35166912
Once again...he's the man!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question