Ignoring non-filled formulas in average
 

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!

Ignoring non-filled formulas in average
 

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

On Jan 26, 12:50*pm, Laurizio
wrote:
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!

Ignoring non-filled formulas in average
 

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" wrote:

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

On Jan 26, 12:50 pm, Laurizio
wrote:
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!

Ignoring non-filled formulas in average
 

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

On Jan 26, 1:13*pm, Laurizio
wrote:
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" wrote:
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

On Jan 26, 12:50 pm, Laurizio
wrote:
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!- Hide quoted text -

- Show quoted text -

Ignoring non-filled formulas in average
 

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" wrote:

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!

Ignoring non-filled formulas in average
 

Yes, that was it!

Excellent - many thanks!





"Pete_UK" wrote:

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

On Jan 26, 1:13 pm, Laurizio
wrote:
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" wrote:
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

On Jan 26, 12:50 pm, Laurizio
wrote:
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!- Hide quoted text -

- Show quoted text -

Ignoring non-filled formulas in average
 

You're welcome - thanks for feeding back.

Pete

On Jan 26, 1:34*pm, Laurizio
wrote:
Yes, that was it!

Excellent - many thanks!