I have in A1:A100 either numbers or #N/A. In B1:B100 there is either
TRUE or FALSE. I want to calculate the average of A1:A100 but only of
those rows where there is an actual number (Not #N/A) and where
condition in column B is true.

I need this to be made in one formula without adding any more column in
the sheet! is it possible ?

Thanks

Paolo


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=569791

=AVERAGE(IF((NOT(ISNA(A1:A10)))*(B1:B10),A1:A10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"kayard" wrote in
message ...

I have in A1:A100 either numbers or #N/A. In B1:B100 there is either
TRUE or FALSE. I want to calculate the average of A1:A100 but only of
those rows where there is an actual number (Not #N/A) and where
condition in column B is true.

I need this to be made in one formula without adding any more column in
the sheet! is it possible ?

Thanks

Paolo


--
kayard
------------------------------------------------------------------------
kayard's Profile:
http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=569791

One way ..

Place in say, C1's formula bar, then array-enter the formula by pressing
CTRL+SHIFT+ENTER (instead of just pressing ENTER):
=AVERAGE(IF((ISNUMBER(A1:A100))*(B1:B100=TRUE),A1: A100))

Adapt the ranges to suit
--
Max
Singapore (.. celebrating our 41st birthday today! [9 Aug 2006])
http://savefile.com/projects/236895
xdemechanik
---
"kayard" wrote:

I have in A1:A100 either numbers or #N/A. In B1:B100 there is either
TRUE or FALSE. I want to calculate the average of A1:A100 but only of
those rows where there is an actual number (Not #N/A) and where
condition in column B is true.

I need this to be made in one formula without adding any more column in
the sheet! is it possible ?

Thanks

Paolo


--
kayard
------------------------------------------------------------------------
kayard's Profile: http://www.excelforum.com/member.php...o&userid=24716
View this thread: http://www.excelforum.com/showthread...hreadid=569791

=AVERAGE(IF(ISNUMBER(A1:A100),IF(B1:B100,A1:A100)) )

which you need to confirm with control+shift+enter, not just with enter.

kayard wrote:
I have in A1:A100 either numbers or #N/A. In B1:B100 there is either
TRUE or FALSE. I want to calculate the average of A1:A100 but only of
those rows where there is an actual number (Not #N/A) and where
condition in column B is true.

I need this to be made in one formula without adding any more column in
the sheet! is it possible ?

Thanks

Paolo