monthly average graph made from daily entry data

just wondering if its at all possible to show a monthly average figure from daily data entries
util.png

what i would like is to show the utilisation average for jan , feb , mar etc etc
the document has lots of columns and charts already in place , they all show daily and weekly figures thou . so if possible id like to see the monthly average

Many users are not able to view .png files on this forum - it would be better to attach a sample Excel workbook.

Pete

this would be a simple version

i just need monthly average and totals as described in tab 2
by month only , not year as the main sheet will be copied and filled in from scratch every year


simple version.xlsx

See attached ...

thats exactly what i need , brilliant thanks

but

ive tried to put those formulas into my despatch log ( 10mb big ) changing the rows in the formula to match what i need and the name of the sheet it needs to look at and it wont work

pic.jpg

I see that for the "error" counts the text is "NCF A SHIFT": the formula I used assumed the A(,B,C,D) is the last letter (right-most) in the cell as per your example. One problem of having test data which does not match the "real world"!

Try changing to "NCF SHIFT A".

Unless I have an exact copy of your real data layout it is difficult to say what (else) is wrong.

i can send you the exact copy no problem

i didnt because of the size of it :-)

Just send a subset showing the format ( I don't need 10,000 rows!)

this is an exact copy of the real data i need it to sort

thanks for your patience

One problem is blanks in data Column AG: correct that _ I'll work on the other formulae.

See attached: I changed entries in row 107 to be dates (01/01/2015 etc) as it is easier to check than using text. They are displayed as "January" etc.

Sorry it has taken this time to resolve: bad day at the "office".

ha ! i know that feeling , its most days in my place of work

thanks for your help - i will have a look when i get home and see if it works . how do i get the formulas into my " real " sheet ??

much much appreciated

ok im home

i have filled the blanks in for column ag
thats one problem i will come up against - one of the inputters missed out columns that he was supposed to fill in

here is the new version :-)

More data errors: one in AG and several in AD.

I have added the formulae - you need to correct data. And change ranges as you add data.Note that blank dates are treated as 0 so date is 01/01/1900 so you will get erroneous results in January.

the 0% utilization figure is unfortunatley correct , it means somebody ordered a wagon to come in to collect 26 pallets and we had no stock available , 0 % means we had to pay for transport that was not used . somebody would of got a tanned behind for it im sure :-)

The 0% cell was blank so I entered 0% to get round the "value" problem: hence no blanks allowed!