average problems

[peterjb44]

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

 

[AlphaFrog]

Replace your Sum formulas =SUM(B2-A2), with something like this =IF(OR(B2="",A2=""),"",B2-A2)
The IF formula will return a blank cell if B2 or A2 is empty. It will return a number or zero if they are not empty.

Then you can Average the cells with
=AVERAGE(Current!E2:E35)
Zeros will be included in the average but not blank cells.

 

[markgordonis]

This might work:

Sheet1

	A	B	C	D	E	F
1	booked in date	investigation date	bookin to invest	date quoted	invest date/quoted date	no dates entered?
2	01/01/2012	15/01/2012	14	16/01/2012	1	date
3	01/01/2012	01/01/2012	0	01/01/2012	0	date
4			0		0	no date
5	01/01/2012	15/01/2012	14	16/01/2012	1	date
6				SUMIF	2
7				Countif	3
8				average	0.666666667

<thead>
</thead><tbody>
</tbody>

Excel 2010

Worksheet Formulas Cell Formula C2 =SUM(B2-A2) C3 =SUM(B3-A3) C4 =SUM(B4-A4) C5 =SUM(B5-A5) E2 =SUM(D2-B2) F2 =IF(A2="","no date","date") E3 =SUM(D3-B3) F3 =IF(A3="","no date","date") E4 =SUM(D4-B4) F4 =IF(A4="","no date","date") E5 =SUM(D5-B5) F5 =IF(A5="","no date","date") E6 =SUMIF(F2:F5,"date",E2:E5) E7 =COUNTIF(F2:F5,"date") E8 =E6/E7 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[peterjb44]

Thankyou Alphafrog, that worked a treat....the way i hoped it could be done

Thankyou markgordonis for your reply too


btw is there a way of removing the weekends from the results and only have working days???

 

[Aladin Akyurek]

Thankyou Alphafrog, that worked a treat....the way i hoped it could be done

Thankyou markgordonis for your reply too


btw is there a way of removing the weekends from the results and only have working days???

Try to post a small sample with the result you want?

 

[AlphaFrog]

Thankyou Alphafrog, that worked a treat....the way i hoped it could be done

Thankyou markgordonis for your reply too


btw is there a way of removing the weekends from the results and only have working days???

You're welcome.

Replace the B2-A2 portion in the IF formula with the Link:NETWORKDAYS function. It calculates workdays (excludes Sat-Sun) between two dates and can also exclude holidays.

Example:
=IF(OR(B2="",A2=""),"",NETWORKDAYS(A2,B2,Holiday_List))

Note:If you have Excel 2010, there is a new version of Networkdays called ​Link:NETWORKDAYS.INTL that allows you more flexibility to define the workweek if you don't want to be limited to Saturday-Sunday weekends.

 

[peterjb44]

thanks again alphafrog for replying......

i'm getting a #name? error

i noticed this on the 1st link you gave...

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.
How?

• On the Tools menu, click Add-Ins.
• In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
• If necessary, follow the instructions in the setup program.

i dont have a tools menu, but i found the add-ins in file-options-then a box opened with add-ins in.....i highlighted analysis toolpak and clicked go (next to manage and the drop down option was on excel add-ins) then i ticked the analysis toolpak then ok............rechecked and now analysis toolpak is active........but i'm still getting the #name? error

thats what i've done plus i copied your example into my speadsheet

btw i have excel 2010 and i'm a novice, lol

 

[AlphaFrog]

It sounds like you installed the Analysis ToolPak properly.

If you copied my formula verbatim, the reference to Holiday_List was not intended to be literal. You have to include you own holiday list reference to specific cells that has your list of holiday dates.

=IF(OR(B2="",A2=""),"",NETWORKDAYS(A2,B2,Z1:Z10))

In this example, the Z1:Z10 has your list of holiday dates or you can exclude the Holidays List argument. It's an optional argument.

 

[peterjb44]

great thats now working, i'm using the argument for bank holidays etc

thanks again for all your time and help

peter