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 :). Mathias 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)) 
Home  More Excel (VBA) Training Videos 