CSE Formula Prob
 

Good morning --
I'm trying to write an array formula that computes the minimum ratio between
one number and the sum of several columns, across about 50 rows of data. I
want to avoid problems that could occur when either the numerator or
denominator doesn't exist (because the user hasn't acquired that much data
yet). I got it to work checking to see if the denominator exists, with this
formula, which appeared to work:

{=MIN(IF(SUM('Practice Data'!$M$6:$V$53)<0,'Practice
Data'!$AI$6:$AI$53/SUM('Practice Data'!$M$6:$V$53),""))}

However, when I tried to check to see if the numerator existed as well, I
got a #VALUE! returned. Here's the offending formula:

{=MIN(IF(AND('Practice Data'!$AI$6:$AI$53<0,SUM('Practice
Data'!$M$6:$V$53)<0),'Practice Data'!$AI$6:$AI$53/SUM('Practice
Data'!$M$6:$V$53),""))}

Thanks in advance.

CSE Formula Prob
 

Not real sure what you're trying to do with this but the problem is caused
when M:V is empty or the sum of M:V = 0 then you're getting:

=MIN("") = #VALUE!

Remove the "" from the formula then you'll get 0.

--
Biff
Microsoft Excel MVP


"pdberger" wrote in message
...
Good morning --
I'm trying to write an array formula that computes the minimum ratio
between
one number and the sum of several columns, across about 50 rows of data.
I
want to avoid problems that could occur when either the numerator or
denominator doesn't exist (because the user hasn't acquired that much data
yet). I got it to work checking to see if the denominator exists, with
this
formula, which appeared to work:

{=MIN(IF(SUM('Practice Data'!$M$6:$V$53)<0,'Practice
Data'!$AI$6:$AI$53/SUM('Practice Data'!$M$6:$V$53),""))}

However, when I tried to check to see if the numerator existed as well, I
got a #VALUE! returned. Here's the offending formula:

{=MIN(IF(AND('Practice Data'!$AI$6:$AI$53<0,SUM('Practice
Data'!$M$6:$V$53)<0),'Practice Data'!$AI$6:$AI$53/SUM('Practice
Data'!$M$6:$V$53),""))}

Thanks in advance.

CSE Formula Prob
 

Sorry -- should have given an example

A B C D
1 1 1 1 2
2 3
3 1 1 1
4 2 2 2 4

If the sum of columns A-C < 0, and there is data in column D, then I want
the minimum product of the SUM(A:C)*D

Here's the formula I'm trying in this simple example:

{=MIN(IF(AND(SUM(A1:C4)<0,E1:E4<0),SUM(A1:C4)*E1 :E4,""))}

Can't figure it out!

TIA

"T. Valko" wrote:

Not real sure what you're trying to do with this but the problem is caused
when M:V is empty or the sum of M:V = 0 then you're getting:

=MIN("") = #VALUE!

Remove the "" from the formula then you'll get 0.

--
Biff
Microsoft Excel MVP


"pdberger" wrote in message
...
Good morning --
I'm trying to write an array formula that computes the minimum ratio
between
one number and the sum of several columns, across about 50 rows of data.
I
want to avoid problems that could occur when either the numerator or
denominator doesn't exist (because the user hasn't acquired that much data
yet). I got it to work checking to see if the denominator exists, with
this
formula, which appeared to work:

{=MIN(IF(SUM('Practice Data'!$M$6:$V$53)<0,'Practice
Data'!$AI$6:$AI$53/SUM('Practice Data'!$M$6:$V$53),""))}

However, when I tried to check to see if the numerator existed as well, I
got a #VALUE! returned. Here's the offending formula:

{=MIN(IF(AND('Practice Data'!$AI$6:$AI$53<0,SUM('Practice
Data'!$M$6:$V$53)<0),'Practice Data'!$AI$6:$AI$53/SUM('Practice
Data'!$M$6:$V$53),""))}

Thanks in advance.

CSE Formula Prob
 

I think you could greatly simplify things by just using an additional
"helper" column. For example, in J1, enter

=IF(COUNT(A1:D1),SUM(A1:D1)*E1)

and then fill down J1:J4. Finally, take the MIN of that column:

=MIN(J1:J4)

It could be done with a rather involved (array) formula, but such formulas
are hard to debug and maintain than two simple formulas.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

"pdberger" wrote in message
...
Sorry -- should have given an example

A B C D
1 1 1 1 2
2 3
3 1 1 1
4 2 2 2 4

If the sum of columns A-C < 0, and there is data in column D, then I want
the minimum product of the SUM(A:C)*D

Here's the formula I'm trying in this simple example:

{=MIN(IF(AND(SUM(A1:C4)<0,E1:E4<0),SUM(A1:C4)*E1 :E4,""))}

Can't figure it out!

TIA

"T. Valko" wrote:

Not real sure what you're trying to do with this but the problem is
caused
when M:V is empty or the sum of M:V = 0 then you're getting:

=MIN("") = #VALUE!

Remove the "" from the formula then you'll get 0.

--
Biff
Microsoft Excel MVP


"pdberger" wrote in message
...
Good morning --
I'm trying to write an array formula that computes the minimum ratio
between
one number and the sum of several columns, across about 50 rows of
data.
I
want to avoid problems that could occur when either the numerator or
denominator doesn't exist (because the user hasn't acquired that much
data
yet). I got it to work checking to see if the denominator exists, with
this
formula, which appeared to work:

{=MIN(IF(SUM('Practice Data'!$M$6:$V$53)<0,'Practice
Data'!$AI$6:$AI$53/SUM('Practice Data'!$M$6:$V$53),""))}

However, when I tried to check to see if the numerator existed as well,
I
got a #VALUE! returned. Here's the offending formula:

{=MIN(IF(AND('Practice Data'!$AI$6:$AI$53<0,SUM('Practice
Data'!$M$6:$V$53)<0),'Practice Data'!$AI$6:$AI$53/SUM('Practice
Data'!$M$6:$V$53),""))}

Thanks in advance.

CSE Formula Prob
 

Hmmm...

What result do you expect based on your sample?

This array formula** will return 6 where row 1 meets the criteria (I
think!):

=MIN(IF((SUBTOTAL(2,OFFSET(A1:C4,ROW(A1:C4)-ROW(A1),,1))0)*(D1:D4<""),SUBTOTAL(9,OFFSET(A1:C 4,ROW(A1:C4)-ROW(A1),,1))*D1:D4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"pdberger" wrote in message
...
Sorry -- should have given an example

A B C D
1 1 1 1 2
2 3
3 1 1 1
4 2 2 2 4

If the sum of columns A-C < 0, and there is data in column D, then I want
the minimum product of the SUM(A:C)*D

Here's the formula I'm trying in this simple example:

{=MIN(IF(AND(SUM(A1:C4)<0,E1:E4<0),SUM(A1:C4)*E1 :E4,""))}

Can't figure it out!

TIA

"T. Valko" wrote:

Not real sure what you're trying to do with this but the problem is
caused
when M:V is empty or the sum of M:V = 0 then you're getting:

=MIN("") = #VALUE!

Remove the "" from the formula then you'll get 0.

--
Biff
Microsoft Excel MVP


"pdberger" wrote in message
...
Good morning --
I'm trying to write an array formula that computes the minimum ratio
between
one number and the sum of several columns, across about 50 rows of
data.
I
want to avoid problems that could occur when either the numerator or
denominator doesn't exist (because the user hasn't acquired that much
data
yet). I got it to work checking to see if the denominator exists, with
this
formula, which appeared to work:

{=MIN(IF(SUM('Practice Data'!$M$6:$V$53)<0,'Practice
Data'!$AI$6:$AI$53/SUM('Practice Data'!$M$6:$V$53),""))}

However, when I tried to check to see if the numerator existed as well,
I
got a #VALUE! returned. Here's the offending formula:

{=MIN(IF(AND('Practice Data'!$AI$6:$AI$53<0,SUM('Practice
Data'!$M$6:$V$53)<0),'Practice Data'!$AI$6:$AI$53/SUM('Practice
Data'!$M$6:$V$53),""))}

Thanks in advance.