• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multi Criteria Average

A

AZExcel

Member
I have a problem. I have a project that consists of 3 columns and 5 rows. The 1st 2 columns contain dates ( 5 rows) from and thru, and the last column contains currency.


The 1st row will contain a date period less than 365 days whereas the subsequent rows will contain from and thru dates equal to 365.


My question primary centers around the last right column. As mentioned this column will contain currency.


I am working on a formula that determines if the 1st row contains less than 365 days

then the currency should be prorated over the 365 days. In other words as in the example below if we spent 900 for the 1st period( 106 days), then the amount projected or prorated over a yr would be 3099.06.


The formula I am working on needs to look at the last column, prorate the 1st roll and then provide an average of the subsequent currency( which will varies up to 5 rows and may contain no entry) below is my attempt that only returns the 3099.06 figure. Any help would be appreciated.


=IFERROR(IF(D63-C63<365,E63/((D63-C63))*365, AVERAGE(C63:D63,D63-C63>=365,E63:E67)),"")


1/1/2012 4/16/2012 $900.00

1/1/2011 12/31/2011 $1,000.00

1/1/2010 12/31/2010 $-

1/1/2009 12/31/2009 $-

1/1/2008 12/31/2008 $
 
To get the prorated value of $3099.06, a simpler formula is:

=E63*365/(D63-C63)


Formula works my multiplying the current dollar value by the ratio of year that has passed so far. To get average values for other years then, formula in F64 is:

=AVERAGE(E$63*365/(D$63-C$63),E$64:E64)

Copy down as needed.
 
Luke M,


Thanks for the simplification of the prorating formula. I had to modify your formula recommendation for the numbers in column E.


I failed to mention that I wanted this average to return in 1 cell so the copy down would not work. This is what I did;


=AVERAGE(E$63*365/(D$63-C$63),E$64:E67)


It seems to work. Thanks
 
Ah, sorry for the misunderstanding. Glad to hear you were able to reach a solution, though.

Regards,

Luke
 
I have a follow up question. I have been researching the issue of averaging with zero.

For example, if for the period 01/01/2012-4/18/2012 was zero, and lets add 1234.00 for period 1/1/2010-12/31/2010. The formula returns $744.67.


If I look at the look at the 838 day period which is approx 2.26 yrs, The average is closer to $973.04. Am I over thinking this or should I consider altering the formula to compensate for zero?


1/1/2012 4/18/2012 $-

1/1/2011 12/31/2011 $1,000.00

1/1/2010 12/31/2010 $1,234.00


$744.67
 
Possibly over thinking. The thing is, you are pro-rating the value for the first set of dates to be treated as if a whole year had occurred. Since current amount if $0, this gets pro-rated out to 0 for the whole year. <-- This is the part that may need to be changed.


You're then taking the average of 3 years (again, first line get's pro-rated to count as a full year) and that's where the $744.67 is coming from.


Options are to

a) Accept as is, and understand that earning $0 so far is causing a low projection

b) Adjust formula so that when $0 earned, divide by partial year amount
 
Thanks Luke,


I did not consider how a zero in the 1st row or any row for that matter would affect the integrity of the numbers, and how prorating although a good idea when dealing with actual earnings, not so much when no earnings have occurred.


After looking at how the earnings would avg over the entire period. A co worker and I stumbled upon this


=IF(YEAR(C63)-YEAR(E61)>= 4,(SUM(E63:E67)/(D63-C67)*365),(SUM(E63:E67)/(D63-E61)*365))


which returns the $973.04.


I think this may be the most efficient way to accomplish our goal, but I will work with it to see if there is something I am not considering.


Thank you again for your help and I welcome your comments or suggestions
 
Hi ,


I assume your dates are in columns C and D ; I do not know what D63-E61 will result in.


However , what I would like to say is that if your dates are in columns C and D , then the total number of days is the difference between the maximum date and the minimum date.


Pro-rating the total using this total number of days should give you the correct result.


=SUM(Dollar_Amounts)*365/((MAX(Dates_Range)-MIN(Dates_Range)))


Narayan
 
Narayan,


You are correct about columns C&D. The D63-E61 accomplishes max date - min date.


Your formula works perfectly and seems to be more efficient and simpler than the one

that me and a co worker came up with.


Thank you... this is the best forum on the planet!!!
 
You must log in or register to reply here.
Back
Top