Ignoring non-filled formulas in average

  • Thread starter Thread starter Laurizio
  • Start date Start date
L

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!
 
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
 
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!
 
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
 
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
 
Yes, that was it!

Excellent - many thanks!





Pete_UK said:
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
Click to expand...
 
Back
Top