MINIF and MAXIF?? 2

[davidbeach]

What would be really handy for me would be to use the MINIF and MAXIF functions, except they don't seem to exist. What I want is to use the same construction as a SUMIF equation, but replace SUMIF with MINIF and then with MAXIF to find the min and max values in a range that meet the criteria.

Any suggestions?

 

[cowski]

Can you give an example of the criteria you need?

 

[Denial]

You might be able to get partial satisfaction using MAX or MIN as an array function.

For example, if your data is in cells C5:C50 and you want the maximum value that is less than whatever you have entered in cell B3, then use
MAX(C5:C50*(C5:C50<B3))
The inner set of parentheses is necessary so that the logical comparisons are done first. This reduces the C5:C50<B3 part to an array of 1s and 0s, which is then multiplied element-by-element into the array C5:C50.

Note that this formula, once entered as an array function, will appear inside {braces}. This is how Excel indicates an aray function.

This approach has a few limitations:
» It will work only for positive numbers.
» It will fall over if C5:C50 contains any non-numerics.
» You are somewhat restricted in the sorts of criteria you can apply.

You might be able to get around some of these limitations with some imaginative constructions.

 

[IDS]

This post from Daily Dose of Excel:

http://www.dailydoseofexcel.com/archives/2004/06/29/maxif-minif-functions/

covers the subject in some detail. Read through the comments (all 55 of them, spread over 6 years!) because there is useful extra information in there.

Also have you considered using the sadly neglected DMAX and DMIN functions?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[davidbeach]

A partial example:

	       {N}                          {O}           {P}         {Q}      {R}
{763}                                                             0.00%    1.25%
{764}   Murrayhill-Scholls Ferry     0.000514756   0.001478386    1.25%    9.81%
{765}   Keeler-Oregon City           0.000514756   0.001225175    1.25%    9.81%
{766}   Murrayhill-Sherwood #1       0.000257378   0.001027642    1.25%    9.81%
{767}   Murrayhill-Sherwood #1       4.00138E-05   0.000159765    9.81%   11.14%
{768}   Keeler-Oregon City           0.002095865   0.005327544   11.14%   45.99%
{769}   Murrayhill-Sherwood #1       0.001047933   0.004184118   11.14%   45.99%
{770}   Keeler-Oregon City           0.002278844   0.005792664   45.99%   83.89%
{771}   Murrayhill-Sherwood #1       0.001139422   0.004549412   45.99%   83.89%
{772}   Scholls Ferry-Sherwood       0.002278844   0.004849994   45.99%   83.89%
{773}   M-P-S North Line             1.6145E-05    2.44774E-05   83.89%   84.32%
{774}   M-P-S South Line             1.59151E-05   2.13968E-05   83.89%   84.32%
{775}   Murrayhill-Sherwood #1       1.7292E-05    0.85913E-05   83.89%   84.32%
{776}   M-P-S North Line             0.000446798   0.000677388   84.32%   96.31%
{777}   M-P-S South Line             0.000440435   0.000592138   84.32%   96.31%
{778}   Murrayhill-Sherwood #1       0.00047854    0.001344718   84.32%   96.31%
{779}   Scholls Ferry-Sherwood       0.000880618   0.001902112   84.32%   96.31%
{780}   M-P-S North Line             7.19409E-05   0.000135872   96.31%   98.38%
{781}   M-P-S South Line             7.0879E-05    0.000122017   96.31%   98.38%
{783}   Murrayhill-Sherwood #1       8.14451E-05   0.000235901   98.38%  100.00%
{784}   Murrayhill-Scholls Ferry     0.000514756   0.001478386		
{785}   Keeler-Oregon City           0.004889465   0.012345383		
{786}   M-P-S North Line             0.000534884   0.000837738		
{787}   M-P-S South Line             0.000527229   0.000735552		
{788}   Murrayhill-Sherwood #1       0.003062024   0.011550147		
{789}   Scholls Ferry-Sherwood       0.003159463   0.006752106

Rows and columns in the {}. Rows 763-783 are the data to be summarized and rows 784-789 are the summary. For columns O and P, I need the sum of each label in column N. The formula in O784 is

=SUMIF($N$763:$N$783,$N784,O$763:O$783)

This is then copied to the rest of the block of cells O784-P789.

That part all works.

Then Q784 needs to be the lowest number in column Q for anything "Murrayhill-Scholls Ferry" or 1.25% in this case. P764 then needs to be the largest number in column P for anything "Murrayhill-Scholls Ferry" or 9.81%. Rather trivial for this one, but row 785 is a bit more complex, needing to be 1.25% in Q785 and 83.89% in R785.

So, what I'd like to do is take the formula in O784, copy it to Q784 and change SUMIF to MINIF and copy it to R784 with MAXIF.

Sorting the data is impossible, much more would be lost than gained, but I'd rather not have to arrive at the Q784-R789 values by eyeballing the data and putting =Q764 in Q784.

This problem occurs hundreds of times in this particular workbook.

 

[davidbeach]

The stuff at

http://www.dailydoseofexcel.com/archives/2004/06/29/maxif-minif-functions/

looks promising.

 

[IDS]

David, you might find this useful as well:

http://newtonexcelbach.wordpress.com/2009/09/09/using-the-dmax-function/

which links back here to:

http://eng-tips.com/viewthread.cfm?qid=253286&page=1

The blog post gives an array formula as well as details of using DMax and DMin, but if you do decide to use the D functions be sure to read the comments about the correct way to enter the criteria.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[cowski]

Looks to be a case where a database might be the more appropriate tool.

 

[IDS]

Looks to be a case where a database might be the more appropriate tool.

Yes, why stick with what you know and adopt one of several suggested simple solutions when you could move to a whole new platform and have the stimulation and interest of finding solutions to a new set of problems in an unfamiliar environment?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[cowski]

Possibly a pivot table? I have not used them often, but if I remember correctly it will group your data by column {N} and then you can operate on subsets of your data.

Doug,
You have given some very helpful advice and links. A star for your efforts. Since the data is already in Excel it is the obvious way to go rather than scrapping and starting over in a database. My database comment was made late at night (for me) and I should have taken the time to expand on it.

If you run these operations regularly, perhaps you should look into a database solution. It would be relatively simple to set up queries and run reports on such data. I have seen people jump through a lot of hoops to get Excel to act like a database. Sometimes the best tool for the job isn't your favorite.

 

[davidbeach]

Don't know how a database would simplify things, perhaps this one piece of the larger puzzle, but certainly not the whole thing. The whole thing is for transmission line data calculations. Calculations start out in MathCAD, far too complex to even begin to think about doing in excel. The results go into an excel component, saved off as individual files. Then those files are opened, various bits copied and pasted as links into the summary spreadsheet. What was seen above is a bit of mutual coupling portion. Each chunk where the values in Q and R are the same is a single section of the line, sections need to stay in order so I can't sort or pivot table. Database would seem to greatly increase the complexity of getting segment data out of MathCAD into a complete line summary.

 

[IDS]

If you run these operations regularly, perhaps you should look into a database solution. It would be relatively simple to set up queries and run reports on such data. I have seen people jump through a lot of hoops to get Excel to act like a database. Sometimes the best tool for the job isn't your favorite.

I absolutely agree with that. It's just that sometimes the message that Excel isn't the right tool for everything comes across as Excel isn't the right tool for anything, so I like to even up the balance a little from time to time

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[JAE]

What about DMAX?