6

I have the following data which is sorted as:

Activity_ID Employee Count
A 10
B 8
C 4

This is suppose to be top 10 sorting; I want to replace the last Activity value ( C ) with "other" word instead of the original value. Can anyone tell me how to do this?

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Sally El-Sayed
  • 161
  • 1
  • 5
  • 11
  • Do you want to replace the `Activity_ID` "C" with another word or `Employee Count` of 4 with another word? Are you specifically looking to replace `Activity_ID` "C", or the last activity in the list? – LittleBobbyTables - Au Revoir Jul 09 '12 at 16:25
  • for each Activity_ID there is Employee_count value, i want to sort Employee Count as top 10 sorting , i want to replace the value of Activity_ID that is equivalent to the least value in the sorting with Other value instead of the original value which is "C", so Yes i want to replace the last activity in the least with "Other" value – Sally El-Sayed Jul 10 '12 at 08:49

2 Answers2

5

You can use the IF statement in a new cell to replace text, such as:

=IF(A4="C", "Other", A4)

This will check and see if cell value A4 is "C", and if it is, it replaces it with the text "Other"; otherwise, it uses the contents of cell A4.

EDIT

Assuming that the Employee_Count values are in B1-B10, you can use this:

=IF(B1=LARGE($B$1:$B$10, 10), "Other", B1)

This function doesn't even require the data to be sorted; the LARGE function will find the 10th largest number in the series, and then the rest of the formula will compare against that.

LittleBobbyTables - Au Revoir
  • 32,008
  • 25
  • 109
  • 114
  • 1
    i don't want to change the text if it is "C", i want to replace the text if the value is in the employee_count is the least value according to the top 10 sorting – Sally El-Sayed Jul 10 '12 at 08:46
3

You can use the Conditional Formatting to replace text and NOT effect any formulas. Simply go to the Rule's format where you will see Number, Font, Border and Fill.
Go to the Number tab and select CUSTOM. Then simply type where it says TYPE: what you want to say in QUOTES.

Example.. "OTHER"

JC Guidicelli
  • 1,296
  • 7
  • 16
Tony
  • 39
  • 1
  • Can you elaborate your answer? Because I tried your solution and it's not working... – Wilson Silva Dec 01 '20 at 09:06
  • 1
    Adding `;;;"Other"` instead of `"OTHER"` worked for me. – user3733912 Jan 19 '21 at 15:09
  • "OTHER" works only for numeric values. ;;;"Other" as mentioned above works for string values as well. This only changes what can be seen visually though. The actual value (e.g. used for filtering) is still retained. – birch3 May 18 '22 at 03:05