Conditional formula results

  • Thread starter Thread starter Scott M.
  • Start date Start date
S

Scott M.

I would like to caluclate MIN and MAX on a column of numbers based on text
in another column. For example, the numbers are in column B and column Q has
"test failed" or some other text in it or no text at all. I want to include
values from column B and calculate the MIN and MAX if column Q has "test
failed" in that row and in another cell, calculate MIN and MAX if column Q
has anything other than "test failed".

Any help you can offer is appreciated.

Scott in Ottawa
 
R1: =IF(Q1="test failed",MAX(B:B),"")
S1: =IF(Q1="test failed", MIN(B:B),""

T1: =IF(Q1<>"test failed", MAX(B:B),"")
U1: =IF(Q1<>"test failed", MIN(B:B),"")
 
you could use another column and have an "if" query in it ie...

=if(Q2="test failed","",B2)

then perform min and max query on the new column.
 
=MIN(IF(Q1:Q6="test failed",B1:B6))

confirm with control shift enter as this is an array formula

similar for MAX


Mangesh
 
Back
Top