Hi all, i've been trying to do a spreadsheet with a chart that ignores zero in averages which i have now done, but i just relised that i will also need to include some zero's in the averages as the spreadsheet is filled in (unless i leave it a day at each process to move to the next part)
lets say in A1 title is booked in date, B1 investigation date, C1 is time taken from booking in to investigation, D1 date quoted, E1 is time take between investigation date and quoted date.......there are more but this will do to show my problem.
my formulas are shown in BOLD
on a blank sheet C2 =sum(B2-A2) and E2 =sum(D2-B2) have zeros in down to C35 =sum(B35-A35)and E35 =sum(D35-B35) as no date have been inserted in A2, B2 and D2
i used this to ignore zeros for my averages for my chart =SUM(Current!C2:C35)/COUNTIF(Current!C2:C35,"<>0")
same again =SUM(Current!E2:E35)/COUNTIF(Current!E2:E35,"<>0")
here my problem, if the booked in date is the same as the investigation date then time taken from booking in to investigation is zero and this will be ignored in my chart which means the averages will be wrong, this is also the same for quoted too and all 3 processes can be done on the same day
any ideas????
peter
lets say in A1 title is booked in date, B1 investigation date, C1 is time taken from booking in to investigation, D1 date quoted, E1 is time take between investigation date and quoted date.......there are more but this will do to show my problem.
my formulas are shown in BOLD
on a blank sheet C2 =sum(B2-A2) and E2 =sum(D2-B2) have zeros in down to C35 =sum(B35-A35)and E35 =sum(D35-B35) as no date have been inserted in A2, B2 and D2
i used this to ignore zeros for my averages for my chart =SUM(Current!C2:C35)/COUNTIF(Current!C2:C35,"<>0")
same again =SUM(Current!E2:E35)/COUNTIF(Current!E2:E35,"<>0")
here my problem, if the booked in date is the same as the investigation date then time taken from booking in to investigation is zero and this will be ignored in my chart which means the averages will be wrong, this is also the same for quoted too and all 3 processes can be done on the same day
any ideas????
peter