Calculating total of highest 8 values in row

Microsoft Excel 2007 home and student
March 23, 2010 at 09:20:24
Specs: Windows XP
I am trying to find a way to get Excel to calculate and highlight the top 8 results across a range of values in a row - otherwise I have to work it out myself which seems laborious - but I am not clever enough to figure it out! Can any of you clever poeple out there help?

See More: Calculating total of highest 8 values in row

March 23, 2010 at 12:11:45
You can highlight them with Conditional Formatting.

Let's assume your values are in A1:L1

1 - From the Ribbon select Home - Styles - Conditional Formatting,
2 - From the drop down select 'Manage Rules' and select 'New Rule'
3 - In the dialog box select the last item in the list 'Use a formula to determine which cells to format'
4 - Enter this formula in the box:


5 - Note that TRUE and FALSE do not have double quotes - they are Excel's logical values TRUE and FALSE.
6 - Click the format button and from the 'Fill' Tab select a color, click OK until out.

To SUM them, try this:

= SUM(IF(COUNT(A1:L1)<8,A1:L1,LARGE(A1:L1,{1,2,3,4,5,6,7,8})))


Nevermind, that won't work if there are any ties.

Try this Array formula instead.


Input the formula without the { } and then use Ctrl-Shift-Enter. Excel will put { } around the formula and use it as an Array Formula.

Report •
Related Solutions

Ask Question