Can it be done? Conditional Highlighting for Top 3 Values in Mult. Cells

Hi Mr Steve,

Not by pressing 'command-C, then selecting all of the sales for one day only'

Command-C placed the currently selected data on the clipboard. If you haven't yet selected any thing,then nothing will be copied to the clipboard.

"I have many Associates that I track Sales and Margin done for each Day of the Week. I enter the data across an entire spreadsheet in Multiple Cells in different areas on the Spreadsheet. "

If that's true, you'll have a difficult time collecting the data for a specific weekday. If your data is better organized than your description implies, what you want can be done relatively easily.

Here's an example using the least complex arrangment of only the data required for the highlighting to occur.

Column A of Data are entered values—the names of the sales associates. Column B on this table also contains entered date, but for your 'real life' case, these would be calculated from the individual sales on the day being tracked.

Columns A and B of Calculations contain the formulas below, entered in A2 and B2 as noted, and filled down as described below the formulas:

A2: RANK(Data::B,Data::B,largest-is-high)

This calculates the rank of each associate's sales figure within the list of all figures in that column.

B2: =LOOKUP(ROW()−1,A,Data::A)

Filled down to row 4. Cell Fill colours entered from Cell Format panel.

The three copies lookup the name in Data, column A associated with each of the top three ranks (1, 2, 3) in column A of Calculations, and copy that name into the cell containing the formula.

Conditional Highlighting:

Cells in Data::column A have the conditional highliting rules shown below:

CONCEPT ONLY for the moment. The highlighting will fail when two or more of the top three scores are duplicates. Easily corrected, but I'll wait for more details on th structure of your data before adding that to the mix.

Regards,

Barry

Mr_Steve_83 wrote:

Select a Group of Cells and use Conditional Highlighting to have the Top 3 Values highlighted.

A script can do this for you. No need to have any experience with AppleScript. Just copy-paste the script, select the cells, and click.

1. Copy-paste script below into Script Editor (in Applications > Utilities)
2. Select the range of cells within which you want to highlight the top 3 values.
3. Click the 'run' button in Script Editor.

Here I clicked A2:A9 and clicked the 'run' button. Four cells are highlighted in this example because there were two with a high ranking that had the same value. Just change the number in the first line of the script if you want more or fewer than the top 3.

If you decide the script is useful and you want it handy in the future you can put it in the script menu or in the Numbers > Services menu. Post back if you need guidance on how to do that. (It's easy.)

SG

set ranksToHighlight to 3

tell application "Numbers"

tell front document's active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

set vv to selRng's cells's value

set hVals to my getHighVals(vv, ranksToHighlight)

repeat with c in selRng's cells

tell c to if its value is in hVals then ¬

set its background color to "yellow"

end repeat

end tell

end tell

end tell

to getHighVals(vv, n)

copy vv to tempVals

set highVals to {}

repeat n times

if (count tempVals) > 0 then

set hv to RANK1(tempVals)

copy hv to highVals's end

set tempVals to removeItem(hv, tempVals)

end if

end repeat

return highVals

end getHighVals

to removeItem(theItem, aList)

set l to {}

repeat with i in aList

if i's contents is not theItem then ¬

copy i's contents to l's end

end repeat

return l

end removeItem

to RANK1(aList)

set h to aList's item 1

repeat with v in aList

set h to MAX(h, v)

end repeat

return h's contents

end RANK1

to MAX(a, b)

if a > b then return a

return b

end MAX