Solved

# YEARFRAC question part duex

Posted on 2011-03-16
470 Views
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
Question by:singleton2787
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 50

Expert Comment

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

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

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

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

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

## Featured Post

Question has a verified solution.

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

### Suggested Solutions

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 article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month4 days, 22 hours left to enroll