Developing an Interesting Formula Using Multiple Functions

Home More Excel (VBA) Training Videos

How can solve I this problem?
I have numerical values in cells B2:B8. If 4 or more values in B2:B8 are equal, I wish to display this value in cell B9.
If the values in cell B2:B8 do not have 4 or more values that are the same, I want to ignore the two highest values and get the average of the other 5 cell values.
I would also like to round off the resultant value to the nearest multiple of 10.
Can this be done with an Excel formula or do I need to write VBA code. I would prefer to work without VBA :).


Watch the video below to see how the interesting formula was developed step by step:

The formula: =IF(COUNTIF(C2:C8,MODE.SNGL(C2:C8))>=4,MODE.SNGL(C2:C8),MROUND((SUM(C2:C8)-(LARGE(C2:C8,1)+LARGE(C2:C8,2)))/COUNT(C2:C8)-2,10))

Watch this video on YouTube

Home More Excel (VBA) Training Videos