HELP!!!!

gymwrecker · May 20th, 2002, 08:45 PM

I have the following:

A1=230
A2=240
A3=153
A4-170
A5=210
A6=0
A7=214

B1=Passed
B2=Passed
B3=Failed
B4=Failed
B5=Failed
B6=Not Tested
B7=Passed

How could I have C1 reflect the average from the above by adding the scores of only those who have "Passed" and "Failed", and obtaining an average of both? If I use all, Passed, Failed and Not tested, I wont have an accurate number, I only need those two, but to add the numbers?

Thanks!

Yogi Anand · May 20th, 2002, 08:57 PM

use the following array formula:

=AVERAGE(IF(B1:B7<>"Not Tested",A1:A7))

Regards!

Aladin Akyurek · May 20th, 2002, 09:53 PM

=SUMIF(B1:B7,"<>Not Tested",A1:A7)/MAX(1,COUNTIF(B1:B7,"<>Not Tested"))

computes the desired average.

May 20th, 2002, 08:57 PM	#2
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	use the following array formula: =AVERAGE(IF(B1:B7<>"Not Tested",A1:A7)) Regards! __________________ Regards! Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC www.energyefficientbuild.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 20th, 2002, 08:45 PM	#1
gymwrecker Board Regular Join Date: Apr 2002 Location: Fort Knox, KY Posts: 249	I have the following: A1=230 A2=240 A3=153 A4-170 A5=210 A6=0 A7=214 B1=Passed B2=Passed B3=Failed B4=Failed B5=Failed B6=Not Tested B7=Passed How could I have C1 reflect the average from the above by adding the scores of only those who have "Passed" and "Failed", and obtaining an average of both? If I use all, Passed, Failed and Not tested, I wont have an accurate number, I only need those two, but to add the numbers? Thanks!

May 20th, 2002, 09:53 PM	#3
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	=SUMIF(B1:B7,"<>Not Tested",A1:A7)/MAX(1,COUNTIF(B1:B7,"<>Not Tested")) computes the desired average.