Solved

YEARFRAC question part duex

Posted on 2011-03-16
5
479 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
[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
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

635 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