Conditional Max Min 1

[shyam131]

How do you use a formula to create a conditional maximum or minimum similar a conditional sum? Is there a maxif function?

 

[ETID]

Look in the help...at the dmax() function...

it's very dynamic

 

[ETID]

....Oooops there is also a dmin(), dsum(), daverage()...dstdev()...and more

 

[shyam131]

I messed around with dmax, dmin. But I am not sure exactly how to implement them.. Let me explain better with an example:

Company Total Segment
1 23 A
2 32 C
3 45 A
4 23 Q
5 23 B

So my pivot table has "Company" and "Total" I have added the segment column next to the pivot table. What I need is to find the max "Total" for all companies except for companies in segment Q. Exactly how would I implent the usage of Dmax, Dmin??????

 

[DaleWatson123321]

shyam131,

I've developed a working model based on the example you provided.

It includes formulas with the =DMAX and =DMIN functions.

I also included an example of an "extraction" option - where VBA code is attached to two separate macro buttons. One is for extracting the company(s) with the MAXIMUM Total, and the other for the MINIMUM Total. Optionally, the routines could be altered to have the data filtered-in-place instead of being extracted to a separate sheet.

In your description, you say you want the Max Total for all companies except for companies in segment Q. This was not a problem. However, I'm wondering if you want: a) the Max Total for "all" companies, or b) the Max Total for "each" company. The model I created is for "all" companies - as per your description, but it could be changed.

I could describe the steps involved in setting up these formulas and extraction process. However, it'll likely be easier for you to grasp if I email you a copy of the file.

For others who might also be interested in the file, just email me and I'll return the file.

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

If you put the segment INTO the pivot table ie as part of the base data, one of the options you can set is to view different versions of the data - specifically MAX & MIN

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

You might try an array formula, I have based the following on the example date you have given us:

{=MAX(($C$2:$C$11<>&quot;Q&quot*($B$2:$B$11))}

The section is located in C2:C11 and the values are located in B2:B11. This returns the maximum for all of the companies in the list except for the companies in section Q.

Enter this formula without the curly brackets, they will be added automatically when you press Ctrl+Shift+Enter after writing the formula instead of just pressing Enter.

I hope this helps!





Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I just realized that you could also write the above as a &quot;normal&quot; formula:

=SUMPRODUCT(MAX(($C$2:$C$11<>&quot;Q&quot*($B$2:$B$11)))

Sorry!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

I tried to do the same as above as a conditional MIN() function, but it wouldn't work. I couldn't figure out why until I realized that the SUMPRODUCT() function was returning &quot;0&quot; for the matches to the first condition ($C$2:$C$11<>&quot;Q&quot and of course the minimum from the list was returning 0.

I then modified the formula, using two SUMPRODUCT() functions and the SMALL() function:

=SUMPRODUCT(SMALL((C2:C11<>&quot;Q&quot*(B2:B11),SUMPRODUCT((C2:C11=&quot;Q&quot*1)+1))

The SMALL() functions syntax is SMALL(number_range,nth_number) (i.e. it will return the nth smallest number in an array of numbers.

Since the records where the section was Q were returning 0 that meant that I had to return the number of Q's in the list of sections plus one to get the minimum for the other values.

Unfortunately this conditional MIN() solution will not work correctly if the minimum number for the other sections is a negative number.

I hope that this is understandable.

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[shyam131]

Bowers74,

I am trying to implement your solution as an array formula, {=MAX(($C$2:$C$11<>&quot;Q&quot*($B$2:$B$11))} The max works great, but using the same formula with min does no seem to work
{=MIN(($C$2:$C$11<>&quot;Q&quot*($B$2:$B$11))}???? The formula returns 0.

Thanks for your help

 

[Bowers74]

Don't use the array formula solutions, use the SUMPRODUCT() solutions. I explained in my last post why the MIN() won't work in an array formula.

Sorry for the confusion!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!