Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and the Yahoo Answers website is now in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
Excel® Spread Sheet question?
I use the formula:
=mode (A1:A45)
for the most common number of a column and
=mode (A1:E45)
for the most common number on a page.
But how do I resolve the top 5 numbers of a particular page.
What formula do I use to get the top 5 numbers or must I use 5 separate formulas and 5 separate cells?
To Greg G:
(If you are reading this somehow the system deleted my other question about this)
1 Answer
- Greg GLv 78 years agoFavorite Answer
I got your email regarding the circular reference error you were getting.
I replied back, so you should have my email address if you want to forward the spreadsheet to me so I can see what's going on with it. If you don't, send me another message and I will give you my email again.
EDIT:
I have a little better solution for you, reworked for your range A1:E45
I put my formulas in column R, but you can put them in any column. If you use another column, change the R's in the array formula to match.
Start with Sheet 1 (assuming you are still doing this over multiple pages), R2:
=IFERROR(MODE(A1:E45, Sheet2!A1:E45), "")
Now, in R3:
=MODE(IF(ISNA(MATCH($A$1:$E$45, $R$2:$R2, 0)), $A$1:$E$45), IF(ISNA(MATCH(Sheet2!$A$1:$E$45, $R$2:$R2, 0)), Sheet2!$A$1:$E$45))
This is an array formula, so confirm with CTRL + SHIFT + ENTER.
Copy that down to R6 and you will have your top 5 most frequently occurring values on both pages.
Let me know if you require further assistance with this. I've tested it on random #'s and it works (no circular references.)
By the way.. I can still see your old question.. it's still out there:
