I'm working with Excel 2007
I would like to create the following formula:
1. 10 cells
2. If 5 or more of the cells have the same number it will give me that number
3. if 5 of the numbers aren't the same the formula will drop 2 cells with the highest numbers and give me the average of the remaining 8 cells
I hope this makes sense........is this formula possible?
Thank you for the help!!!
Last edited by bramberg; 07-22-2013 at 10:58 AM. Reason: Mods request
|5|
|2|
|4|
|7|
|5|
|5|
|5|
|5|
|1|
|3|
1.) Search through the row and find the most repeated number used... MODE <- Math function
2.) Search through the row again and see how often that number was used.
3.) IF that number was greater or equal to >= 5 (TRUE) return the MODE
4.) IF (FALSE) You can use a RANK to sort the list from least to greatest
5.) Once that is done you can grab the first 8 and do an AVG on those.
Hi and welcome to the forum
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem. Once you have done this please send me a PM and I will remove this request.
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sorry to sound like a newbie here but what would I type in the cell to make this formula work? I only have experience with the basic formulas and I would still consider myself a beginner.
Thank you again for the reply!
It's not Shakespeare, but this should work (assuming your 10 cells are A20:A29):
=IF(COUNTIF(A20:A29,MODE(A20:A29))>=5,MODE(A20:A29),(SMALL(A20:A29,1)+SMALL(A20:A29,2)+SMALL(A20:A29,3)+SMALL(A20:A29,4)+SMALL(A20:A29,5)+SMALL(A20:A29,6)+SMALL(A20:A29,7)+SMALL(A20:A29,8))/8)
Pauley
--------
If I helped with your issue, I'd appreciate a rep bump (hit the '*' icon to the bottom left of this post).
Maybe this...
=IF(MAX(FREQUENCY(A1:A10,A1:A10))>=5,MODE(A1:A10),AVERAGE(SMALL(A1:A10,{1,2,3,4,5,6,7,8})))
Assuming there will always be at least 8 numbers in the range.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks for the assist guys
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks