CSE Formula Prob

[pdberger]

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.

 

[T. Valko]

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.

 

[pdberger]

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

 

[Chip Pearson]

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

=IF(COUNT(A11),SUM(A11)*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)

 

[T. Valko]

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)*(D14<>""),SUBTOTAL(9,OFFSET(A1:C4,ROW(A1:C4)-ROW(A1),,1))*D14))

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