Prev: Date + relative number reference
Next: Is there a function to solve for an unknown in an equation?
From: Rene on 26 Apr 2010 08:35
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
From: Mike H on 26 Apr 2010 08:47
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.


"Rene" wrote:

> 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
From: Jacob Skaria on 26 Apr 2010 08:55
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))


--
Jacob (MVP - Excel)


"Rene" wrote:

> 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
From: Rene on 26 Apr 2010 09:14
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"

"Mike H" wrote:

> 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.
>
>
> "Rene" wrote:
>
> > 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
From: Bob Phillips on 26 Apr 2010 12:51
Select the cell, then menu Format>Cells>Number and select General. Then hit
F2 and then Enter. Should clear it.

--

HTH

Bob

"Rene" <Rene(a)discussions.microsoft.com> wrote in message
news:8D0C5D22-2A78-46E9-9AA7-BCA21B1A535C(a)microsoft.com...
> 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"
>
> "Mike H" wrote:
>
>> 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.
>>
>>
>> "Rene" wrote:
>>
>> > 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


 | 
Pages: 1
Prev: Date + relative number reference
Next: Is there a function to solve for an unknown in an equation?