# Excel formula...not for the fainthearted.

Vindict 13:17 22 Dec 08
Locked

Hey all

I have a sheet that calculates time over a 17 week working period taking into account peoples hours and breaks. In the totals I use the simple [h]:mm formula. I need to calculate an exact 48 week average over 17 weeks. Please download the file to see what I mean

The 'Average Hrs per week, for reference period' as shown in the example has calculated the time with the notion that there are 100 minutes in an hour, any ideas?

Simsy 18:04 22 Dec 08

when I have more time, but the immediate thing I notice is that the cell you refer to is NOT formatted [h]:mm

I guess that's an error?

I'm not certain what you mean by;
"I need to calculate an exact 48 week average over 17 weeks"

Can you spell out, very clearly what you mean by that, please.

Regards,

Simsy

Picklefactory 18:46 22 Dec 08

I'm also not clear on what is required here. More detail please?

recap 19:20 22 Dec 08

I think Vindict means pro rata.

Zak 19:43 22 Dec 08

As Simsy says the formatting is not consistent. All time needs to be correctly formatted as [hrs]:min.

Weeks to remain as numbers.

In your formula Hours available you need to multiply by 48:00; best to have this value formatted in [hrs]:min in a separate cell.

Average Hrs per week, for reference period then as I see gives the average number of hours to be worked for the remaining weeks out of the 17. in your case there are 16 weeks left and 50:33 per week now has to be worked to achieve the 816:00 figure.

Average Hrs Fixed 7:00
Average Hrs per week, for reference period 50:33

Hrs completed 7:00
Weeks completed 1.00
Hrs available for period 816:00 48:00

Weeks In Period 17.00

Vindict 22:00 22 Dec 08

The sheet is supposed to calculate how many hours someone has worked and how many hours remain for somebody to work over a 17 week period. Every time a weeks worth of information is added the sheet should show how many hours can be worked weekly over the remaining weeks.

The problem I have had is getting a formula to calculate time in hours accurately agaisnt the figure 17 (for 17 weeks).

This has become a little addiction of mine to get solved because nobody I have spoken to believes it can be done. Any help is really appreciated this started out as a challenge but is now becoming the bane of my life.

daba 01:27 23 Dec 08

Vindict - I have played with this and have a modified file that does what you want.

Click on my envelope and I can e-mail you the new file.

daba 01:40 23 Dec 08

Vindict - I have played with this and have a modified file that does what you want.

Click on my envelope and I can e-mail you the new file.

daba 01:41 23 Dec 08

apologies for the double-post above....

Vindict 06:31 23 Dec 08

Dropped you across a message, Christmas may be coming a day early.

daba 16:31 23 Dec 08

I've sent Vindict a modified file I believe will help him.

Vindict's problem solved by the use of [h]:mm formatting as Simsy pointed out, and by doing the time calculations using cell pointers, not by quoting time values in the formulas themselves.

HTH

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Prehistoric Britain is laid out in these Royal Mail stamp illustrations

Best running headphones | Best sport & fitness headphones: 4 brilliant pairs of wireless…