I have a spread sheet that counts how many days and hours have past from a
set date and time entered into another field. I would like to show averages
but for some reason I get #DIV/0!. Here is the formula that counts the days
and hours: =INT(NOW()-K18)&" Days, "&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve

It worked for me, but can be simplified to

=INT(NOW()-K18)&" Days, "&ROUND(MOD(NOW()-K18,1)*24,0)&" Hours"

What is in K18?


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve COR" wrote in message
...
I have a spread sheet that counts how many days and hours have past from a
set date and time entered into another field. I would like to show
averages
but for some reason I get #DIV/0!. Here is the formula that counts the
days
and hours: =INT(NOW()-K18)&" Days,
"&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve

I think the reason you get #div/0 is that the field is a text field
On what cells and formulas do you do your average calulation.


"Steve COR" wrote:

I have a spread sheet that counts how many days and hours have past from a
set date and time entered into another field. I would like to show averages
but for some reason I get #DIV/0!. Here is the formula that counts the days
and hours: =INT(NOW()-K18)&" Days, "&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve

The fields are number formated. I guess as Bob pointed out I may have been a
bit unclear.
K18 was the cell I copied to create this message, showing the formula.
Basically what I have is a spreadsheet that has maybe fourty/fifty rows each
one with a project start date and a project completion date. The below
formula tells me how many days and hours that specific project took. What I
need to do is average all those into how many days it averages for a specific
project.

I really appreciate this, thank you...

"bj" wrote:

I think the reason you get #div/0 is that the field is a text field
On what cells and formulas do you do your average calulation.


"Steve COR" wrote:

I have a spread sheet that counts how many days and hours have past from a
set date and time entered into another field. I would like to show averages
but for some reason I get #DIV/0!. Here is the formula that counts the days
and hours: =INT(NOW()-K18)&" Days, "&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve

one way would be to use
=INT(AVERAGE(NOW()-K:K))&" Days "&INT(MOD(AVERAGE(NOW()-K:K),1)*24)&" Hours"

"Steve COR" wrote:

The fields are number formated. I guess as Bob pointed out I may have been a
bit unclear.
K18 was the cell I copied to create this message, showing the formula.
Basically what I have is a spreadsheet that has maybe fourty/fifty rows each
one with a project start date and a project completion date. The below
formula tells me how many days and hours that specific project took. What I
need to do is average all those into how many days it averages for a specific
project.

I really appreciate this, thank you...

"bj" wrote:

I think the reason you get #div/0 is that the field is a text field
On what cells and formulas do you do your average calulation.


"Steve COR" wrote:

I have a spread sheet that counts how many days and hours have past from a
set date and time entered into another field. I would like to show averages
but for some reason I get #DIV/0!. Here is the formula that counts the days
and hours: =INT(NOW()-K18)&" Days, "&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve

=INT(AVERAGE(NOW()-K2:K20))&" days
"&ROUND(MOD(AVERAGE(NOW()-K2:K20),1)*24,0)&" hours"

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Steve COR" wrote in message
...
The fields are number formated. I guess as Bob pointed out I may have been
a
bit unclear.
K18 was the cell I copied to create this message, showing the formula.
Basically what I have is a spreadsheet that has maybe fourty/fifty rows
each
one with a project start date and a project completion date. The below
formula tells me how many days and hours that specific project took. What
I
need to do is average all those into how many days it averages for a
specific
project.

I really appreciate this, thank you...

"bj" wrote:

I think the reason you get #div/0 is that the field is a text field
On what cells and formulas do you do your average calulation.


"Steve COR" wrote:

I have a spread sheet that counts how many days and hours have past
from a
set date and time entered into another field. I would like to show
averages
but for some reason I get #DIV/0!. Here is the formula that counts the
days
and hours: =INT(NOW()-K18)&" Days,
"&ROUND((NOW()-K18-INT(NOW()-K18))*24,0)&"
Hours"
I need to find the average days and hours
thank you for anyhelp in advance, Steve