Solved

YEARFRAC question part duex

Posted on 2011-03-16
5
436 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Once again...he's the man!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now