3 Replies Latest reply: May 15, 2014 10:02 AM by KarenG _

# Creation of Summary field problem

Hi

I am trying to ascertain the average of two fields within my summary.  Initially i used the 'Average' function, but that is not giving back the result that i am having to calculate to.

I have a 'count' column - which is a measure - which is all the instances that relate to a certain operator

i have a 'time to complete' - which is a calculated field in the table and in time format - which is the lenght of time that operator took to resolve the issue.

an example of my problem:

Operator                            1

Count                                83

Time                                 18886

Average in Monarch             310

Average in Excel                  228

The average column has the formula AVG()/60

but if you divide 18886/83 (time/count)you arrive at 228

I tried to use the time/count in my summary formula but as 'count' is a measure it wont allow me to.

Kind Regards

• ###### Creation of Summary field problem

Karen

You can calculate the number of instances in the summary by using a calculated field in the table that only has 1 as its formula and totalling this for the operator with a sum formula in the summary. Then you should be able to use that field to calculate the average as you have already tried, thereby avoiding having to use the count field. You can hide the new table field if you need to.

Regards

Nigel

• ###### Creation of Summary field problem

Karen,

When working with times, especially when doing more than time difference calculation and even then being careful, it is best to convert you values to seconds, sum the number of seconds, calculate the average and then convert back to a time format  - probably HH:MM:SS in most but not all cases.

It's not easy to be sure of what you are dealing with from the information provided.

Is the Time value of 18886 in minutes?

Looks like it might be.

On the other hand your formula refers to a field by a name that suggests ITS value is in hours and minutes. It may not be in hours and minutes but it would be good to check.

Grant

• ###### Creation of Summary field problem

Hi

thanks for the replies.. i managed to do it by using Sum()/COUNT(*)/60.. took a lot of trial and error though.

One more question... on one of the columns i am exporting, even though i have set the format to 'numeric' but it is still exporting with the leading '

how can i stop this?

Karen