Averaging the difference between multiple cells and excluding blank values

[SMAusa]

I have a large data set where I need to find the average difference between two rows, but need to exclude the columns where either cell in the two rows is blank. For example here are just a few columns:

[TABLE="width: 864"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD="align: right"]23:10:00 +00
[/TD]
[TD="align: right"]02:03:00 +01[/TD]
[TD][/TD]
[TD="align: right"]01:44:00 +01[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22:04:00 +00[/TD]
[/TR]
[TR]
[TD="align: right"]23:18:00 +00
[/TD]
[TD="align: right"]02:10:00 +01[/TD]
[TD="align: right"]00:14:00 +01[/TD]
[TD="align: right"]01:51:00 +01[/TD]
[TD][/TD]
[TD="align: right"]01:45:00 +01[/TD]
[TD="align: right"]00:35:00 +01[/TD]
[TD="align: right"]22:10:00 +00[/TD]
[/TR]
</tbody>[/TABLE]

I need to find the average difference between the two rows, but where there is data missing (for example on the third column, there is only a second value but the first is missing, the fifth column is completely blank), I want excel to completely ignore those two columns and not include any part of it in the average.

What formula(s) do I use in order to achieve this?

Any help would be much appreciated!

 

[gaz_chops]

What is the +00 etc

 

[SMAusa]

What is the +00 etc

Sorry, you can ignore that- I am averaging times so the format is hh:mm:ss +dd - we are using the day at the end for times at 12am or later on the following day)

 

[AhoyNC]

See if this might work.
NOTE: This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H
1	23:10:00	2:03:00		1:44:00				22:04:00
2	23:18:00	2:10:00	0:14:00	1:51:00		1:45:00	0:35:00	22:10:00
3
4	Average	0.004861111
Sheet

 

[SMAusa]

See if this might work.
NOTE: This is an array formula and must be entered with CTRL-SHIFT-ENTER.

*	A	B	C	D	E	F	G	H
*	*	*	*
*
*	*	*	*	*	*	*	*
Average	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"><col style="width:97px;"><col style="width:64px;"><col style="width:95px;"><col style="width:64px;"><col style="width:91px;"><col style="width:97px;"><col style="width:115px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]23:10:00[/TD]
[TD="align: right"]2:03:00[/TD]

[TD="align: right"]1:44:00[/TD]

[TD="align: right"]22:04:00[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]23:18:00[/TD]
[TD="align: right"]2:10:00[/TD]
[TD="align: right"]0:14:00[/TD]
[TD="align: right"]1:51:00[/TD]

[TD="align: right"]1:45:00[/TD]
[TD="align: right"]0:35:00[/TD]
[TD="align: right"]22:10:00[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #ffff00, align: right"]0.004861111[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B4 {=AVERAGE(IF(A1:H1<>"",IF(A2:H2<>"",A2:H2-A1:H1),""))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I think that works! Thanks a million!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the form.