How to Provide Ranking ???
In this figure, products B & D are tied with sales of 87. The old
RANK and RANK.EQ functions assign both of those products a rank of 2 and
no product is ranked as 3.
Statisticians argue that products B & D should each receive a rank of 2.5, since the average of ranks 2 & 3 is 2.5. The new Excel 2010 function RANK.AVG will handle ties in this fashion.
Excel tricksters who use RANK to sort with a formula as described in the next topic want to make sure that every rank is used exactly once. They will use the formula shown in column G. This formula uses the original RANK function and then adds 1 if the ranked value is appearing a second time in the list.
=RANK($B2,$B$2:$B$8)+COUNTIF(B$2:B2,B2)-1
Courtesy By MrExcel.com
Excel FLOOR Function and CEILING Function
Excel has many great functions to allow you to round numbers but FLOOR and CEILING are two pretty cool ones.
While most functions allow you to round to a certain decimal place, these functions allow you to pick what you want to round to. If you need to round to large numbers like the nearest 100 or nearest 500, these functions can do it.
Let’s say we have these numbers below. We want to round the numbers in Column A to the nearest number in column B.
In row 2, we want to round 111 to the nearest 10. What the FLOOR function will do is round it DOWN to the nearest 10. The CEILING functions will round it UP to the nearest 10.
So the function would be:
We could also manually type the numbers in and use =FLOOR(111,10) and get the same result. But references work just as well.
See how Excel takes 111 and rounds it down to the nearest 10 which is 110.
For the CEILING function we would use:
This works similar as FLOOR, except it rounds the number UP to the nearest 10 in this case. So we get 120.
This will work to round for any instance. You can round the the nearest 50, or 100 or 372. It is up to you! See below for our final results:
Courtesy By Joe.
REPLACE Function in Excel
REPLACE function in excel replaces a set of characters in a text string with a different set of characters, based on the number of characters specified.
At times, imported or copied data in an Excel spreadsheet carries unwanted characters or words, which can be replaced with good data using REPLACE function.
REPLACE function makes it easy to quickly rectify large amount of data. The function can be created for the first entry and then copied to all other cells, as done for most Excel functions.
REPLACE Function Example
Area code updation in phone number
REPLACE function can be used to update the first three digits in a phone number, on area code undergoing a change. In this example, Column C contains the new area code, Column D has the revised phone numbers.
REPLACE Function Syntax
The syntax for the REPLACE function is:
=REPLACE(old_text, start_num, num_chars, new_text)
Old_text – Text where some characters needs to be replaced. This can be a cell reference to the text.
Start_num - Mentions the start position (left to right) of the characters in old_text that needs to be replaced.
Num_chars - Mentions the number of characters to be replaced from the Start_num specified above.
New_text – Text that will replace characters in old_text. This should be left blank if just unwanted characters need to be removed.
Text values that CONVERT Function accepts
Listed below are text values that CONVERT Function accepts for from_unit and to_unit (in quotation marks) :
| WEIGHT AND MASS | FROM_UNIT / TO_UNIT |
| Gram | “g” |
| Slug | “sg” |
| Pound mass (avoirdupois) | “lbm” |
| U (atomic mass unit) | “u” |
| Ounce mass (avoirdupois) | “ozm” |
TIME
| FROM_UNIT / TO_UNIT |
| Year | “yr” |
| Day | “day” |
| Hour | “hr” |
| Minute | “mn” |
| Second | “sec” |
DISTANCE
| FROM_UNIT / TO_UNIT |
| Meter | “m” |
| Kilometer | “km” |
| Statute mile | “mi” |
| Nautical mile | “Nmi” |
| Inch | “in” |
| Foot | “ft” |
| Yard | “yd” |
| Angstrom | “ang” |
| Pica | “pica” |
FORCE
| FROM_UNIT / TO_UNIT |
| Newton | “N” |
| Dyne | “dyn” (or “dy”) |
| Pound force | “lbf” |
TEMPERATURE
| FROM_UNIT / TO_UNIT |
| Degree Celsius | “C” or “cel” |
| Degree Fahrenheit | “F” or “fah” |
| Kelvin | “K” or “kel” |
LIQUID MEASURE
| FROM_UNIT / TO_UNIT |
| Teaspoon | “tsp” |
| Tablespoon | “tbs” |
| Fluid ounce | “oz” |
| Cup | “cup” |
| U.S. pint | “pt” or “us_pt” |
| U.K. pint | “uk_pt” |
| Quart | “qt” |
| Gallon | “gal” |
| Liter | “l” or “lt” |
PRESSURE
| FROM_UNIT / TO_UNIT |
| Pascal | “Pa” or “p” |
| Atmosphere | “atm” or “at” |
| mm of Mercury | “mmHg” |
ENERGY
| FROM_UNIT / TO_UNIT |
| Joule | “J” |
| Erg | “e” |
| Thermodynamic calorie | “c” |
| IT calorie | “cal” |
| Electron volt | “eV” or “ev” |
| Horsepower-hour | “HPh” or “hh” |
| Watt-hour | “Wh” or “wh” |
| Foot-pound | “flb” |
| BTU | “BTU” or “btu” |
POWER
| FROM_UNIT / TO_UNIT |
| Horsepower | “HP” or “h” |
| Watt | “W” or “w” |
MAGNETISM
| FROM_UNIT / TO_UNIT |
| Tesla | “T” |
| Gauss | “ga” |
CONVERT Function is used to convert data from one measurement unit to another.
For example, converting time from hours to minutes, distance from miles to kilometers, temperature from degrees Celsius to degrees Fahrenheit etc.
CONVERT Function Example
In this example, we’ll be converting table values from hours to minutes
=CONVERT(B3, “hr”, “mn”)
- Number : B3 ( Cell reference for the number to be converted)
- From_unit : “hr” (Converted from unit – hours)
- To_unit : “mn” (Converted to unit – minutes)
CONVERT function result : 600 in cell C3
CONVERT Function Syntax
=CONVERT(number, from_unit, to_unit)
Number : The value to be converted.
From_unit : The measurement unit for Number.
To_unit : The measurement unit for the converted value (result).
Excel mixes up data types at times and sees numerical data as text data. For eg. when you import data into Excel from another program / internet.
In such a situation, functions like SUM or AVERAGE lead to calculation errors.
VALUE Function converts numbers seen as Text to Values and make them function normally for SUM, AVERAGE and other numerical functions.
The syntax for the VALUE function is: = VALUE ( Text )
Text – Data to be converted. It can be actual data in quotation marks or a cell reference.
FIXED Function in Excel
Excel FIXED function returns number rounded to a specified number of decimal places as text. It uses commas as per specification.
SYNTAX
The syntax for the Microsoft Excel FIXED function is:
FIXED( number, [decimal_places], [no_commas] )
number is the number which has to be rounded.
decimal_places is optional. If ithis parameter is omitted, decimal places is assumed to be 2.
no_commas is optional. If set to TRUE, the result will not display commas. If set to FALSE, it will display commas in the result. If omitted, the result will display commas.
REPT Function in Excel
REPT(text, number_times) : Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.
Text - The text you want to repeat.
Number_times - The number of times to repeat text.
TRIM Function in Excel
TRIM(text) : Removes all spaces from a text string except for single spaces between words
Non Single Spaces
Courtesy By Systemfunda.com
