average problems

peterjb44

New Member
Joined
Jul 7, 2012
Messages
16
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
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

New Member
Joined
Dec 25, 2010
Messages
41
This might work:

Sheet1
ABCDEF
1booked in dateinvestigation datebookin to investdate quotedinvest date/quoted dateno dates entered?
201/01/201215/01/20121416/01/20121date
301/01/201201/01/2012001/01/20120date
400no date
501/01/201215/01/20121416/01/20121date
6SUMIF2
7Countif3
8average0.666666667

<thead>
</thead><tbody>
</tbody>
Excel 2010

Worksheet Formulas
CellFormula
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

New Member
Joined
Jul 7, 2012
Messages
16
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
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

New Member
Joined
Jul 7, 2012
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,376
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

New Member
Joined
Jul 7, 2012
Messages
16
great thats now working, i'm using the argument for bank holidays etc :)

thanks again for all your time and help

peter
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top