Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup prob with all #n/a's | Excel Discussion (Misc queries) | |||
If statements and conditions - again!! new prob | Excel Worksheet Functions | |||
Macro Prob | Excel Worksheet Functions | |||
Counting prob | Excel Discussion (Misc queries) | |||
A little help, prob simple to anyone else | New Users to Excel |