Microsoft Excel 2007 home and student

Hi

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?

~~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:=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.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History