Excel 2007 : =MAXIF and =MINIF equivalent function please

**barney_g** · 03-21-2010, 01:11 AM

I have a table with positive and negative numbers in a column named "PercentDiff" with many different values for each day of the month. For example on the March 19 there were a total of 359 rows of data on this day.

I need to create two formulas for two cells (Max and Min) that will return the Max value and Min value of this "PercentDiff" column of numbers on this particular date which I'll update manually in cell “M1” from time to time.

I type in the number of the date that I’m interested in this cell “M1” that I want to use as a filter of a column called "Date" which is the day of the month. In this example “19”.

I've used “=COUNTIF(Date,M1)” to give me the total number of records (rows) on this day and “=COUNTIFS(Date,M1,PercentDiff,">0")to tell me the number of positive numbers for one cell and “=COUNTIFS(Date,M1,PercentDiff,"<0")” for the negative numbers.

Now all I need to know is just how large and how small the values are on this day in the "PercentDiff" column. Kind of the equivalent of a “=MAXIF” or “=MINIF” functions if they existed.

I tried using an Array but I understand you cannot use a named column range in an ever expanding table for an Array.

I hope that I explained it well enough to understand.

Can anyone help me please?

Thank you

**teylyn** · 03-21-2010, 01:23 AM

Hi barney,
I don't see why you cannot use an array formula for that

=max(if((date=M1)*(PercentDiff>0),PercentDiff,0))

confirmed with Ctrl-Shift-Enter

**barney_g** · 03-21-2010, 01:13 PM

Perfect! It worked great.

I cannot thank you enough. I've been toiling over this forever. I guess I was missing something in the formula/function. I did read somewhere that you cannot refer to a column range of say for example "B:B" So I wrongly assumed that the problem I was having was in naming this column.

Obviously more study is needed on my part on Arrays.

Thanks again

PS. One more question, how can I display an identifying value in an adjacent column for the MAX and MIN numbers within that row.

For example, if the MAX value on this date is X, I want to know if it was Joe or someone else associated with that MAX value on that Date. Same for MIN value.

Thanks in advance

**teylyn** · 03-21-2010, 05:17 PM

Hi, use a Vlookup or Index/Match to return the value of a different column.

With your names in column B and your array formula as above in C1, use something like

=index(C:C,match(C1,dates,0))