what should be simple

  • Thread starter Thread starter Rene
  • Start date Start date
R

Rene

b2 =min(g2,o2,w2,ae2)
g2 =k2/n2
o2 =s2/v9
....

b2 works when all the cell formulas have data to compute, but does not work
when one of the formulas returns the error msg #div (no data)

Thanks for your help
 
Hi,

Maybe something like the in G2 & O2

=IF(N2<>"",K2/N2,"")
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Either you can modify the formulas in g2,o2,w2,ae2 to handle the error.

Instead of =S2/V9 modify that to =IF(COUNT(S2,V9)=2,S2/V9,"")


OR use the below formula to return MIN() . Please note that this is an array
formula. You create array formulas in the same way that you create other
formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If
successful in 'Formula Bar' you can notice the curly braces at both ends like
"{=<formula>}"

=MIN(IF((MOD(COLUMN(G2:AE2)+1,8)=0)*(ISNUMBER(G2:AE2)),G2:AE2))
 
Thanks Mike. It seems to work. I'll test the whole database to be sure :)
Another question: the index formula messed up when I extended the database
and now returns the formula itself instead of the result

=index(i2:at2,match(b2,g2:at2,o)

Trying to use a formula that will return the text of a cell in the min group
"winner"
 
Select the cell, then menu Format>Cells>Number and select General. Then hit
F2 and then Enter. Should clear it.
 
Back
Top