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 Im 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
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
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
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
Last edited by barney_g; 03-21-2010 at 02:45 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))
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks