#VALUE! Question and Sum Average Question

[mullyoo1]

Gosh, not sure I can articulate this request clearly without confusing the reader (apologies). All the data has been sensitively edited and all my questions refer to the 'Reports&Charts' Tab

Firstly I would like to be able to 'average' the process times for each month and populate the revelant cells as per the other calculations ie early, on time and late.
Secondly, for those units which haven't been processed I currently recieve a #VALUE! in the cell (particularly in the two lower charts). Ideally I do not want these not to be included in the count for early, late and on-time result but to be shown seperately in another cell called 'still to process'
is this possible

[modytrane]

Look at the attached file.
Middle block has been cleaned up.
Note formulas in all the cells.
Hope it helps.
modytrane

[mullyoo1]

Firstly thank you very much for your time and help!
Not sure I have explained myself very well, the middle section is now clear but the data isn't correct...when I manually check 38 units were processed five were early ie -3 days and 33 were on-time ie 0 days, but the chart doesn't show this.
I am still struggling with the #VALUE! question and the average time....
When I read this back it sounds as though I am being ungreatful for the help but that could not be further from the truth it's just that I have been bagging my head against the computer for the last few days now

[DonkeyOte]

RE: VALUE -- this is because your source range for the Sumproduct (Column I) generates VALUE errors... you need to revise your formulae on the source sheets such that error values are avoided, ie:

MPS2 P'formance!I2:
=IF(COUNT(G2:H2)<2,"",NETWORKDAYS(G2,H2,NonProdDays!$A$1:$A$100)-1)
copy down for all rows

[DonkeyOte]

Re: AVERAGE... not entirely clear but if you want to Average Column I on source sheet per month then you can do a SUMIF based on month and divide by the count of planned units which you've already calculated in C, eg:

Reports & Charts
I17: =IF(N($C17),SUMIF('MPS2 P''formance'!$B$2:$B$2500,MONTH($B17),'MPS2 P''formance'!$I$2:$I$2500)/$C17,NA())

copy down as required... this generates NA() for months for which no average can be calculated (in case you intend to plot)

[mullyoo1]

That works wonderfully well thank you very much !!
Could you offer any advise on my other issues..
those being an average calculation by month and for the ones where the cell was #VALUE! but is now blank to be able to count those as still to be processed.

As you can see my excel is limited but I am learning abate slowly

[DonkeyOte]

Again, I'm still not sure I follow ... re: AVERAGE - see my prior post.

Re: Still to Launch

First I think you need to adjust the Late Units formulae given a Null ("") is > 0 ... ie those previously listed as VALUE now NULL ("") will now be showing as Late (>0) so the formula needs revising such that:

Reports & Charts
F17:
=SUMPRODUCT(--('MPS2 P''formance'!$B$2:$B$2500=MONTH($B17)),--(ISNUMBER('MPS2 P''formance'!$I$2:$I$2500)),--('MPS2 P''formance'!$I$2:$I$2500>0))
copy down (& apply to other tables)

My next point would be:

is not the "Still to Launch" value not simply the inverse of the Total vs Plan figure ?

[mullyoo1]

Sometimes I just sit in ore of the help and advise people are willing to offer. Days upon days I have been tussling with this, less and 10 mins with you and it's solved....
Once again THANK YOU VERY MUCH!!

[mullyoo1]

your are correct and now everything works fine
As before many many thanks for your time and patients