array formulas using MIN MAX


C

Christi

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),c1:c4)),MIN((IF((a1:a4="1")*(b1:b4=2009)*(d1:d4<>0),d1:d4)))))}

Can anyone help?
 
Ad

Advertisements

J

Jarek Kujawa

shouldn't MAX for Rating 1 in 2009 equal 4%?
shouldn't MAX for Rating 1 in 2010 equal 4%, too?
 
C

Christi

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%.
 
Ad

Advertisements

L

Lars-Åke Aspelin

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%.


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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top