Ignoring non-filled formulas in average

[Laurizio]

Hi,

I'm trying to generate a list of timespans where I eventually want the
average. The timespans (column C) is calculated by formula (B-A). This list
will be constantly updated so I have prepared the formulas for the cells even
though the dates aren't filled in.

An illustration of my spread sheet is:

Row A B C
1 2009-01-01 2009-01-10 9
2 2009-01-01 2009-01-20 19
3 2009-01-05 2009-01-10 5
4 2009-01-15 2009-01-15 0
5 0 (b5-a5)
6 0 (b6-a6)
....


When I want the average so far it counts with the rows not yet filled in as
well (row 5-6 in the example). My question is; Can I ignore the rows not yet
filled in in any smart way? One option that I have tried is to ignore
everything less than 1 when doing the average formula but then I risk missing
the few ones where the timespan equals 0 (C4 in the example).


Thx for your support!

 

[Pete_UK]

You could have a formula like this in C1:

=IF(OR(A1="",B1=""),"",B1-A1)

and then copy this down. If either (or both) cell is blank it will
return a blank, and this will not be counted in your AVERAGE formula.

Hope this helps.

Pete

 

[Laurizio]

Thanks, that seems plausible.

However, if I type it in the same way it gives me a message saying "the
formula you typed contains an error...". After the message the marker
higlights "",b1 in the formula.

Any idea of what I'm doing wrong?!

Thanks again!

 

[Pete_UK]

If you use a semicolon rather than a comma as delimiter, due to your
regional settings, then you might need to change the formula to this:

=IF(OR(A1="";B1="");"";B1-A1)

Hope this helps.

Pete

 

[Mike H]

Hi,

Why not average column C only if there are entries in columns A & B

=AVERAGE(IF(A1:A20<>"",IF(B1:B20<>"",C1:C20)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[Laurizio]

Yes, that was it!

Excellent - many thanks!

If you use a semicolon rather than a comma as delimiter, due to your
regional settings, then you might need to change the formula to this:

=IF(OR(A1="";B1="");"";B1-A1)

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete