MINIF and MAXIF??
MINIF and MAXIF??
(OP)
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?
Any suggestions?





RE: MINIF and MAXIF??
RE: MINIF and MAXIF??
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.
RE: MINIF and MAXIF??
http
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/
RE: MINIF and MAXIF??
CODE
{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
CODE
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.
RE: MINIF and MAXIF??
RE: MINIF and MAXIF??
ht
which links back here to:
http:/
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/
RE: MINIF and MAXIF??
RE: MINIF and MAXIF??
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/
RE: MINIF and MAXIF??
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.
RE: MINIF and MAXIF??
RE: MINIF and MAXIF??
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/
RE: MINIF and MAXIF??