Averaging

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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...
 
one way would be to use
=INT(AVERAGE(NOW()-K:K))&" Days "&INT(MOD(AVERAGE(NOW()-K:K),1)*24)&" Hours"
 
=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)
 
Back
Top