Conditional MIN and AVERAGE

[babsc01]

Happy Friday! Has anyone ever messed around with compiling a VBA project to mimic the Conditional Sum Wizard? I've messed around with the Wizard to try other arguments, such as MAX, MIN, AVG, etc. The only one that seems to work is MAX:

{=MAX(IF($C$2:$C$681>11031,$E$2:$E$681,0))} 

MIN and AVERAGE don't work, however. I would think they would...oh well.

Have a great weekend...and THANKS.

[TonyJollans]

Not sure about the wizard, but the problem with the formula is the zero if False.

The Array produced by the IF consists of :

• Values from the Range $E$2:$E$681 when the check against corresponding cells in the Range $C$2:$C$681 returns TRUE
• Zeroes when the check returns FALSE

The MIN of these values (assuming they're positive) will be zero (unless the condition happens to be TRUE for every cell).
The AVERAGE of these values will be lowered by the inclusion of all the zeroes.

The SUM and the MAX, on the other hand, are not affected by the extra zeroes in the array.

If you remove the value-if-false part of the IF function so that you just have ..

{=MAX(IF($C$2:$C$681>11031,$E$2:$E$681))} 

.. then the array will consist of:

• Values from the Range $E$2:$E$681 when the check against corresponding cells in the Range $C$2:$C$681 returns TRUE (as before)
• Boolean FALSE values when the check returns FALSE

The FALSEs will be ignored by MAX, MIN, and other Functions so that you should then see the results you want (without needing any code).

[Zack Barresse]

Adding to what Tony's laid out for you, if you want to put Average in there, just replace the Max with it and confirm the formula with Ctrl + Shift + Enter, as it's an array formula (distinguished by 1- the layout, 2- the curly brackets enclosing the formula. If you don't conform w/ CSE, the brackets will disappear).

Incidently I'm actually working on a userform for just that. It's still in the works but I plan on putting it up on the site for critique. So keep an eye out in the near future.

[babsc01]

Wow...I hadn't even considered some of those things in Tony's reply. Food for thought on the rest of the Friday.
Zack...the AVERAGE didn't seem to work, even when I entered it as an array formula. But, I'll keep trying. Thanks!

[Zack Barresse]

Did you put it in like this...

=AVERAGE(IF($C$2:$C$681>11031,$E$2:$E$681)) 

That's how it needs to be entered, then instead of just enter, do Ctrl + Shift + Enter simultaneously. Should work for ya. You could always go with a non-array'd formula, if you're looking for a formula solution that is.

[babsc01]

I did enter as an array formula (Ctrl+Shift+Enter) and it worked. I could swear that I've done that in the past. I've also simply done a FIND & REPLACE throughout the range, which should leave an array formula as is.
I guess we can consider this one solved. This place is great...thanks!

[Zack Barresse]

Glad you got it working! Glad to help.

Still keep an eye out for that userform, coming soon to VBAX!

[babsc01]

Can you divulge any information about it? Sounds like it will be a great tool.

[Zack Barresse]

This is a screenshot of what I've got so far. Only 'bout half way through the coding. I may give the option of using array formula or a non-array formula, but not sure at this point.

Lemme know what you think....

[babsc01]

Very nice...should be great. How about adding COUNT to the available functions? COUNTIF is easy enough to do via formula or status bar, but...as long as you're there.