Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formulas using MIN MAX
I have a worksheet with the following layout:
A B C D Rating Year Rate Override Rate Row1 1 2009 4% 2.5% Row2 1 2010 3% Row3 3 2009 6% Row4 1 2010 4% I'm trying to set up formuals to populate the following: Titles---------- Forumlas---- Rating Year Min Max 1 2009 2.5% 6.0% 1 2010 3.0% 3.0% I can't figure out how to select only one column's value for calculating the Min/Max. D should always replace C, if D is populated. Otherwise only C should be considered. File is much bigger than this, but an example formula I was trying to work with (which is not properly calculating the MIN) is: ={MIN(MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4=0),c 1:c4)),MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4<0) ,d1:d4)))))} Can anyone help? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formulas using MIN MAX
shouldn't MAX for Rating 1 in 2009 equal 4%?
shouldn't MAX for Rating 1 in 2010 equal 4%, too? On 17 Gru, 05:01, Christi wrote: I have a worksheet with the following layout: * * * * * *A * * * * * * * *B * * * * * * * * * *C * * * * * * * * * * *D * * * * *Rating * * * * Year * * * * * * * *Rate * * * * * * * * Override Rate Row1 * * 1 * * * * * * * * 2009 * * * * * * * 4% * * * * * * * * * 2.5% Row2 * * 1 * * * * * * * * 2010 * * * * * * * 3% * * * * * * * * * Row3 * * 3 * * * * * * * * 2009 * * * * * * * 6% * * * * * * * * * Row4 * * 1 * * * * * * * * 2010 * * * * * * * 4% * I'm trying to set up formuals to populate the following: Titles---------- * * * *Forumlas---- Rating * * Year * * * * Min * * * Max 1 * * * * * * 2009 * * * *2.5% * *6.0% 1 * * * * * * 2010 * * * *3.0% * *3.0% I can't figure out how to select only one column's value for calculating the Min/Max. *D should always replace C, if D is populated. *Otherwise only C should be considered. *File is much bigger than this, but an example formula I was trying to work with (which is not properly calculating the MIN) is: ={MIN(MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4=0),c 1:c4)),MIN((IF((a1:a4="1"*)*(b1:b4=2009)*(d1:d4<0 ),d1:d4)))))} Can anyone help? * * * * * * * * |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formulas using MIN MAX
Whoops. Yes, I had some typos when I was simplifying mu jumbo worksheet.
Sorry for the confusion. With what I have here, yes, it should be 4%. The formula still doesn't work, because for if you substitute 2010 for 2009, it will arrive at a min of 0%, when the Min is 3%. "Jarek Kujawa" wrote: shouldn't MAX for Rating 1 in 2009 equal 4%? shouldn't MAX for Rating 1 in 2010 equal 4%, too? On 17 Gru, 05:01, Christi wrote: I have a worksheet with the following layout: A B C D Rating Year Rate Override Rate Row1 1 2009 4% 2.5% Row2 1 2010 3% Row3 3 2009 6% Row4 1 2010 4% I'm trying to set up formuals to populate the following: Titles---------- Forumlas---- Rating Year Min Max 1 2009 2.5% 6.0% 1 2010 3.0% 3.0% I can't figure out how to select only one column's value for calculating the Min/Max. D should always replace C, if D is populated. Otherwise only C should be considered. File is much bigger than this, but an example formula I was trying to work with (which is not properly calculating the MIN) is: ={MIN(MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4=0),c 1:c4)),MIN((IF((a1:a4="1"Â*)*(b1:b4=2009)*(d1:d4< 0),d1:d4)))))} Can anyone help? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array formulas using MIN MAX
On Wed, 17 Dec 2008 10:29:02 -0800, Christi
wrote: Whoops. Yes, I had some typos when I was simplifying mu jumbo worksheet. Sorry for the confusion. With what I have here, yes, it should be 4%. The formula still doesn't work, because for if you substitute 2010 for 2009, it will arrive at a min of 0%, when the Min is 3%. "Jarek Kujawa" wrote: shouldn't MAX for Rating 1 in 2009 equal 4%? shouldn't MAX for Rating 1 in 2010 equal 4%, too? On 17 Gru, 05:01, Christi wrote: I have a worksheet with the following layout: A B C D Rating Year Rate Override Rate Row1 1 2009 4% 2.5% Row2 1 2010 3% Row3 3 2009 6% Row4 1 2010 4% I'm trying to set up formuals to populate the following: Titles---------- Forumlas---- Rating Year Min Max 1 2009 2.5% 6.0% 1 2010 3.0% 3.0% I can't figure out how to select only one column's value for calculating the Min/Max. D should always replace C, if D is populated. Otherwise only C should be considered. File is much bigger than this, but an example formula I was trying to work with (which is not properly calculating the MIN) is: ={MIN(MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4=0),c 1:c4)),MIN((IF((a1:a4="1"*)*(b1:b4=2009)*(d1:d4<0 ),d1:d4)))))} Can anyone help? Try taking away all " from the formula and see if that helps. Also, the - must be a typo. It should be removed if it is there. When I make these changes I get 2.5% as a result. The formula can also be simplified a bit. The following will give the same result ={MIN(IF((A1:A4=1)*(B1:B4=2009),IF(D1:D4<"",D1:D4 ,C1:C4)))} Hope this helps / Lars-Åke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas with-an an array | Excel Worksheet Functions | |||
Help With Array Formulas... | Excel Discussion (Misc queries) | |||
Array Formulas | Excel Worksheet Functions | |||
array formulas | Excel Worksheet Functions | |||
How do I sum a row of array formulas? | Excel Worksheet Functions |