I'm glad that works for you. Since you are averaging
"days remaining", try something like the following
to automatically exclude expired licenses.
=SUMIF([date
range],">"&TEXT(NOW()-365,0))/COUNTIF([date
range],">"&TEXT(NOW()-365,0))
-Ben
James Empey <jamesmp3 EARTHLINK.NET> wrote:
Thanks Ben;
I have;
1/1/2006, 2/1/2006, 5/15/2006, 6/7/2006, 7/31/2006 and using
=AVERAGE(B1:B5)
returns 4/17/2006.
Thanks
-----Original Message-----
From: MS Excel General Q & A List [mailto:EXCEL-GPEACH.EASE.LSOFT.COM] On
Behalf Of Ben Thomas
Sent: Thursday, September 28, 2006 9:35 AM
To: EXCEL-GPEACH.EASE.LSOFT.COM
Subject: Re: Average date formula
Since dates are stored as serial numbers, and you are using
the actual dates
(including year), then you should be able to take the simple
average of a
list of all dates using the AVERAGE() function.
Question: Is there a cutoff after which very old licenses
are no longer
included? If not, then a second license purchased two years
after a first
will expire on the date of purchase.
If you are throwing out old licenses, then you could
calculate with
Sumif(values, greater than cutoff date) / Countif
("" , "").
-Ben
James Empey wrote:
I am struggling with an easy way to calculate renewal dates
for my
customers. When they buy software they get one year of
support free
(embedded in price, I'm not crazy). When they buy a second
license there
anniversary date needs to average from what days are
remaining on the
initial software purchase and the second, third and so on.
Example 1st
license 01/01/2006 = 365 days, 2nd license 02/01/2006 = 365
days. I need to
average these two dates so the real anniversary date for
these two would be
01/15/2007. Does this make sense? OBTW, it might be that a
customer could
have more than two, could be three, four, five and so on.
Thanks
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
---------------------------------
Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone
calls. Great rates
starting at 1¢/min.
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
---------------------------------
Get your own web address for just $1.99/1st yr. We'll help.
Yahoo! Small Business.
------------------------------------------------------------
--------------
The EXCEL-G list is hosted on a Windows NT(TM) machine
running L-Soft
international's LISTSERV(R) software. For
subscription/signoff info
and archives, see htt
p://peach.ease.lsoft.com/archives/excel-g.html .
COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/wa
.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
|