Averaging Cell with Multiple Criteria plus Forced Minimum Values

Hi there, I been pulling hair (what's left of it!) trying to work through what I have been tasked with.

My goal is to get the average value based on the value in 'L4:L18' below, using the search value in 'L2' as shown below. This I can do...

Additionally (where to trouble begins), I need to somehow force the values in 'M4:M18' without physically changing them if they are under $500. So in the example below, averaging 'L' values that equal '22222' would use a value of $500 for 'M11' instead of the listed $350.

So instead of the actual average of $637... the result would be $667, changing the $350 value to $500.

Is something like this possible?

average.PNG


Thanks
Craig

Try this:

=AVERAGE(IF($L$4:$L$18=L2,IF($M$4:$M$18<500,500,$M$4:$M$18)))

Enter with Ctrl+Shift+Enter.

Or this non-array formula:

=(SUMIFS(M4:M18,L4:L18,L2,M4:M18,">=500")+COUNTIFS(L4:L18,L2,M4:M18,"<500")*500)/COUNTIF(L4:L18,L2)

Both solutions worked awesome!

I feel kind of silly how simple you both made it...

Thank you both for your help!
Craig

For what ever reason, once I tried to add this to my real world data... something went wrong.

I have attached a sample workbook the has the calculations to account for the $500 minimum based on the formulas offered earlier (column B) and another column (C) that I have placed $500 in place of values that were under the $500 mark.

I would think that the search averages in B2 and C2 and the average totals in B4 and C4 should match... but they don't, so I must have done something wrong. I also noticed that if I change the ending row value to cover any expansion, the values would change even if the additional rows are blank so may need to add criteria to skip blank cells.

I would appreciate any help in getting me headed in the right direction...

Thanks
Craig

After some more testing... I can now see that my variance is due to some of the rows having no values... which will happen. So I need to add a check in the formula to skip blank cells.

How do I go about adding this check... do I use averageifs? Would need to skip B10:B1000 if a blank cell was found. I have tried changing to averageifs but get errors...

=AVERAGE(IF($A$10:$A$1000=$J$3,IF($B$10:$B$1000<$M$3,$M$3,$B$10:$B$1000)))

Change formula to:

=AVERAGE(IF($A$10:$A$1000=$J$3,IF($B$10:$B$1000<>"",IF($B$10:$B$1000<$M$3,$M$3,$B$10:$B$1000))))

Enter with Ctrl+Shift+Enter.

Excellent... works perfectly!

Thank you so much...

Craig