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?
This question is related to
excel
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.
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"
Source: Stackoverflow.com