Averaging

I and setting up a rodeo spreadsheet and I want to have excellent average 2 days. One cell could have a NT in it if they broke a pattern. Is there a formula where it would consider the NT 0.
Example
A1..........b1....c1........d1....e1
Michelle... NT....michelle..18.56..9.28

Maybe this...

Data Range

	A	B	C	D	E
1	Michelle	NT	michelle	18.56	9.28

This array formula** entered in E1:

=AVERAGE(IF(A1:D1="NT",0,A1:D1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by Tony Valko

If that's your data tell us what result you expect.

The data is first 4 and result would be 9.28. Sorry i was in clear.
Name ,time, name, time, average

But the time colum could contain a nt instead of a time if their was an mistake in the run of the participant.

Originally Posted by Tony Valko

Maybe this...

Data Range

	A	B	C	D	E
1	Michelle	NT	michelle	18.56	9.28

This array formula** entered in E1:

=AVERAGE(IF(A1:D1="NT",0,A1:D1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Sorry
I also have a points column in between the time and name


So
Name,time,points,empty,name, time, point,average. It is picking up the whole part of the row. If I need it to just pick the 2 cells b1,f1. what do I enter.

Looks like this
Name.time.point.empty.name.time.point

A1..........b1.c1.d1...e1......f1....g1

Michelle, nt, 0,"",michelle,18.56,0

It gave me 4.64

Maybe this...

Data Range

	A	B	C	D	E	F	G	H
1	Name	Time	Points	------	Name	Time	Points	Average
2	Michelle	NT	0		Michelle	18.56	0	9.28

=AVERAGE(IF(B2="NT",0,B2),IF(F2="NT",0,F2))

You're welcome. Thanks for the feedback!

You're welcome. Thanks for the feedback!