formula/ calculation in pivot table

M

Michelle B

I am using Excel 2003. I have a spreadsheet that has columns for"Time
Started", "Time Finished" & use these to calulate "Time Taken". In my pivot
table I need to know how many activities took longer than 10 minutes and how
many were either equal to or less than 10 minutes. The data in the
spreadsheet is imported from another system and changes daily. Have been
battling with this for hours now @ would appreciate any ideas?
 
R

Roger Govier

Hi Michelle

Next to your column where you calculate Time Taken, have another column
called 10_Mins_plus
Assuming this is column D and your Time Taken is column C enter
=(C2*24*60)>10
and copy down

This will return TRUE for those rows where the time taken is greater
than 10 minutes.
Excel stores Time as fractions of a day, so multiplying by 24, gives
hours and then by 60 turns the result into minutes.

Incorporate the new column into your PT and select for True or False as
appropriate.
 
M

Michelle B

Gee Wizz, Thank you so much - I have managed to get it working based on your
idea, now I just have to see if I can get the programmers of the logistics
system to write the formulas into their program so that it exports to Excel -
I need them to
come through with the raw data because the length of the speadsheet will
differ eveyday.
Thanks
Michelle
 
R

Roger Govier

Hi Michelle

If we modify the formula to
=IF(C2="","",(C2*24*60)>10)
then you won't get problems if there is no data present in column C.

Even if the programmers don't incorporate if for you, adding the formula
to D2, then double click on the fill handle at the bottom right corner
of D2 and it should fill down the column for you.
 
Top