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

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History