*PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES*

#### CONTENTS

*PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES*

#### CONTENTS

# EXCEL FUNCTION-RANK

## INTRODUCTION

*The RANK function does exactly the same task what its name suggests.*

* The RANK FUNCTION is found under the STATISTICAL CATEGORY of functions in Microsoft EXCEL.*

THE RANK FUNCTION HELPS US TO FIND THE RANK [ POSITION IN A CLASS ] OF ANY NUMBER IN A GROUP OF NUMBERS.

*The Rank is same as the position if the numbers are in the descending order. [ If we give the Rank 1 for the highest value]. *

*But what if the numbers given are not in ascending or descending order. In that case, this function will be of immense help.*

*In this article, we will learn about the purpose, syntax formula, example and other information about the RANK FUNCTION in Excel.*

## PURPOSE OF RANK FUNCTION IN EXCEL

**RANK FUNCTION returns the relative position of the number , by its value, from a group of given numbers. **

**For example,**

**Suppose, we have the following numbers.**

100,4,2,5,7

Out of these, if we go by the standard rules,

RANK 1 FOR THE HIGHEST VALUE,

Rank 1 will be given to 100.

Rank 2 for 7

Rank 3,4,5 for 5,4 and 2 respectively.

EXCEL GIVES US THE OPTION TO FIND OUT THE RANK IN THE REVERSE WAY TOO [ REFER EXAMPLE BELOW]

## PREREQUISITES TO LEARN RANK FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the RANK WORD is an advantage for the use of such formulas.
- Of course, Excel software.

## SYNTAX: RANK FUNCTION

**The Syntax for the RANK function is**

RANK ( NUMBER WHOSE RANK TO BE FOUND , REFERENCE , ORDER )

**NUMBER WHOSE RANK IS TO BE FOUND** This is the number whose rank we want to find.

**RANGE** This is the group of numbers or class in which we want to find the rank.

**ORDER [OPTIONAL] **Order on the basis of which RANKING will be done.

** If 0 or omitted, **Order will be standard. Ranking on the basis of descending values which means HIGHER VALUE will have LOWER RANK.

**If 1, Order will be in Reverse. Ranking will be on the basis of ascending values which means LOWER VALUE will have LOWER RANK.**

## EXAMPLES:RANK FUNCTION IN EXCEL [WITH DESCENDING ORDER ]

### EXAMPLE TYPE 1: FIND OUT THE RANK OF THE GIVEN NUMBER

Let us take a group of various numbers.

WE HAVE TAKEN NUMBERS IN A COLUMN, BUT YOU CAN TAKE A COMPLETE TABLE OF NUMBERS TOO.

93 |

27 |

62 |

82 |

92 |

92 |

100 |

56 |

7 |

51 |

89 |

6 |

11 |

8 |

41 |

80 |

11 |

5 |

56 |

92 |

17 |

53 |

43 |

20 |

60 |

**STEPS TO SUBSTITUTE WORDS IN THE GIVEN TEXT:**

- Select the cell where we want the result.
- Enter the formula =RANK(Number, whose rank is to be found, Range , Order).
- For our example, the formula will be
**=RANK(F8,F$8:H$32,0)**for the first example. - Drag down the formula through the column. The results will appear in the respective column.
- The formula used is shown for the help for every example.

**EXPLANATION:**

The formula used is

**=RANK(F8,F$8:H$32,0)**

**F8 which contains the value 93**[ The number of which rank is to be found ].

**ABSOLUTE RANGE**of the group of numbers in which the rank is to be found. We used the absolute range to avoid messing up while dragging down the formula.

**FIND OUT THE RANKING FROM THE TOP**i.e.From the highest value to the lowest.

EQUAL VALUES ARE GIVEN SAME RANK, BUT THE NEXT RANK WOULD START AFTER THE NUMBER OF EQUAL RANKS.

### EXAMPLE TYPE 2: RANKING NUMBERS IN REVERSE ORDER IN EXCEL

In this step, we would try to find the ranking in the reverse order which is, Lower value would be having the Lower rank or we can say just like the Ascending order.

For the Example, let us take a sample of numbers.

35 | 41 | 27 |

48 | 26 | 24 |

24 | 44 | 49 |

35 | 23 | 50 |

47 | 38 | 23 |

39 | 35 | 26 |

37 | 48 | 42 |

32 | 49 | 27 |

**STEPS TO FIND OUT THE RANK OF A NUMBER IN REVERSE ORDER**

- Select the cell where we want the result.
- Enter the formula =RANK(Number, whose rank is to be found, Range , Order).
- For our example, the formula will be
**=RANK(F56,$F$45:$H$52,1)**for the first example. - Drag down the formula through the column. The results will appear in the respective column.
- The formula used is shown for the help for every example.

**EXPLANATION:**

**=RANK(F56,$F$45:$H$52,1)**

**F56 which contains the value 35**[ The number of which rank is to be found ].

**ABSOLUTE RANGE**of the group of numbers in which the rank is to be found. We used the absolute range to avoid messing up while dragging down the formula.

**FIND OUT THE RANKING FROM THE BOTTOM**i.e. from the last value.

The only difference in this method, from the previous one is the introduction of the ORDER argument which is put in the third place.

The order ID used is 1, which makes the function to RANK the numbers in the Reverse Order.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.

# EXCEL FUNCTIONS-CEILING

## INTRODUCTION

*CEILING FUNCTION has become redundant now and is only given here for the friends who has Excel older than 2010. *

*CEILING.MATH is the new function which has taken its place. CLICK HERE TO READ CEILING.MATH*

*CEILING is quite a useful function in day to day working in programming. It is a special type of ROUND function.*

*It is found under the MATH AND TRIG GROUP of the formulas in MICROSOFT EXCEL.*

*CEILING function rounds up the number to its nearest multiple of significance as mentioned. [suppose significance is 2, then it’ll round the given number to the nearest multiple of 2]*

*CEILING FUNCTION is very useful when we need to round up some number to a custom significance such as rounding some number to the nearest 0.2 significance or anything like that.*

## PURPOSE OF CEILING FUNCTION IN EXCEL

**CEILING function rounds up the number to the next integer to the next nearest multiple of significance.**

***THE SIGNS OF NUMBER AND SIGNIFICANCE SHOULD BE SAME OTHERWISE IT’LL CAUSE AN ERROR.**

## PREREQUISITES TO LEARN CEILING FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

- Basic understanding of how to use a formula or function.
- Basic understanding of rows and columns in Excel.
- Some information about the MATH & TRIG terms is an advantage for the use of such formulas.
- Of course, Excel software.

## SYNTAX: CEILING FUNCTION

**The Syntax for the function is**

**=CEILING.MATH( REFERENCE/VALUE, SIGNIFICANCE)**

**REFERENCE/VALUE **is the cell address or the value, which is to be rounded.

**SIGNIFICANCE** is the number , to which we want to round up our number. The number will be rounded up to the nearest multiple of significance.

## EXAMPLE:CEILING FUNCTION IN EXCEL

### DATA SAMPLE

Let us take a random data to check the FLOOR FUNCTION to various significance.

we’ll round a few numbers of different types to the nearest multiple of significance.

- ROUND 23.2 TO THE NEAREST MULTIPLE OF 1.5
- ROUND -20 TO THE NEAREST MULTIPLE OF -3
- ROUND -20 TO THE NEAREST MULTIPLE OF 3

### STEPS TO USE CEILING.MATH FUNCTION-EXAMPLE

STEPS:

1. Place the cursor in the cell and type the following function

2. USE THE FOLLOWING FUNCTION

FOR

- ROUND 23.2 TO THE NEAREST MULTIPLE OF 1.5
- =CEILING(23.2,1.5)
- This example shows the simple use of CEILING FUNCTION. The number to be rounded is 23.2 and it is to be rounded with the significance of 1.5. So the rounding goes upto 24 and hence the answer.
- ROUND -20 TO THE NEAREST MULTIPLE OF -3
- =CEILING(-20,-3)
- In this example, we want to round the number -20 to the significance of -3. The answer comes to be -21.
- ROUND -20 TO THE NEAREST MULTIPLE OF 3
- =CEILING(-20,3)
- In this example we make the significance positive and it works to round towards the 0 and -18 is the answer.
- NOTE-THE LAST EXAMPLE WILL CREATE AN ERROR IF TRIED IN EXCEL VERSIONS EARLIER TO 2010. As we already mentioned both signs should be same for old EXCEL softwares.

### generalized STEPS TO USE CEILING.FLOOR function

- USE THE FUNCTION
- =CEILING(NUMBER, SIGNIFICANCE)
- Press ENTER and the result will appear.

OTHER WAYS TO REACH THIS ARTICLE

## WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

YOU MAY LIKE

- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.