# Calculating total of highest 8 values in row

Microsoft Excel 2007 home and student
March 23, 2010 at 09:20:24
Specs: Windows XP
 HiI 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

#1
March 23, 2010 at 12:11:45
 You can highlight them with Conditional Formatting.Let's assume your values are in A1:L11 - 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:=IF(RANK(A1,\$A\$1:L\$1)<9,TRUE,FALSE)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})))Edit:Nevermind, that won't work if there are any ties.Try this Array formula instead. {=SUM(IF(COUNT(A1:L1)<8,A1:L1,IF(RANK(A1:L1,A1:L1)<9,A1:L1)))}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