How do I determine the average $ amountscollected on an hourly basis

[Geoff]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)

I am preparing a spreadsheet to record the amounts collected in an appeal together with the hours worked by each collector. So far this works well however I have now decided to include the 'average dollars collected per hour'. Normally this would simply be the amount collected divided by the hours worked.

In this case the hours worked is determined by the formula "=TEXT(.....,"h:mm")and is obviously not a mathematical factor (eg a sum of $400 divided by 2:30 [hrs and minutes] produces a result of $3,840. If I use 'Average()' I get an answer of $400.

Could someone please help me the correct formula?

Thanks

 

[Niek Otten]

Divide your result (3,840) by 24

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Version: 2008
| Operating System: Mac OS X 10.5 (Leopard)
|
| I am preparing a spreadsheet to record the amounts collected in an appeal together with the hours worked by each collector. So
far this works well however I have now decided to include the 'average dollars collected per hour'. Normally this would simply be
the amount collected divided by the hours worked.
|
| In this case the hours worked is determined by the formula "=TEXT(.....,"h:mm")and is obviously not a mathematical factor (eg a
sum of $400 divided by 2:30 [hrs and minutes] produces a result of $3,840. If I use 'Average()' I get an answer of $400.
|
| Could someone please help me the correct formula?
|
| Thanks

 

[CyberTaz]

Hi Geoff -

Unfortunately I don't have the solution off the top of my head, but it may
help to mention that your problem stems from trying to divide one type of
value (Number) by a different type of value (Date/Time). If you convert the
hours & minutes [2:30] to a fractional number [2.5] you should get what
you're looking for.

Alternatively you could convert the number of hrs & minutes [2:30] to
minutes [150], divide by that, then divide the result by 60.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Geoff]

Divide your result (3,840) by 24

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message | Version: 2008
| Operating System: Mac OS X 10.5 (Leopard)
|
| I am preparing a spreadsheet to record the amounts collected in an appeal together with the hours worked by each collector. So
far this works well however I have now decided to include the 'average dollars collected per hour'. Normally this would simply be
the amount collected divided by the hours worked.
|
| In this case the hours worked is determined by the formula "=TEXT(.....,"h:mm")and is obviously not a mathematical factor (eg a
sum of $400 divided by 2:30 [hrs and minutes] produces a result of $3,840. If I use 'Average()' I get an answer of $400.
|
| Could someone please help me the correct formula?
|
| Thanks


Thanks Niek

Whilst I was expecting an excel formula function this will do the trick.

Regards

Geoff

 

[PhilD]

Divide your result (3,840) by 24

Niek Otten
Microsoft MVP - Excel

wrote in message| Version: 2008
| Operating System: Mac OS X 10.5 (Leopard)
|
| I am preparing a spreadsheet to record the amounts collected in an appeal together with the hours worked by each collector. So
far this works well however I have now decided to include the 'average dollars collected per hour'. Normally this would simply be
the amount collected divided by the hours worked.
|
| In this case the hours worked is determined by the formula "=TEXT(......,"h:mm")and is obviously not a mathematical factor (eg a
sum of $400 divided by 2:30 [hrs and minutes] produces a result of $3,840. If I use 'Average()' I get an answer of $400.
|
| Could someone please help me the correct formula?
|
| Thanks

Thanks Niek

Whilst I was expecting an excel formula function this will do the trick.

Regards

Geoff

It *is* an Excel formula function. In the cell that works out your
"average" (the figure that is far too big), just put /24 at the end.

Excel treats times as fractions of a day (so 12:00 is treated as 0.5
days, 6:00 as 0.25 days, and so on), so what you worked out was the
average collection *per day*. Dividing by 24 converts it into the
units you want (hours).

PhilD