Excel Tips - Tip 42 (Using Vlookup with match command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to find a particular outcome from a range, then we uses  “Vlookup with match command”

What Does It Do ?

This function first lookup the data from the range , then it satisfy the outcome and provide the outcome in respect of the lookup value.

How to use it ?

''=vlookup(lookup Values, table array, Match(lookup Values, table array))

For examples please check the below images

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 41 (Using Multiple if command with vlookup)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to satisfy a criteria and want a outcome from particular range if it meets the mentioned criteria, then we uses  Multiple if command with Vlookup.

What Does It Do ?

This function first satisfy the criteria  , that we have put, if the criteria meets then it provide the value in respect of the range of data.

How to use it ?

'=IF(Range,crietria,[sum_range]),TRUE COMMAND '=vlookup(lookup Values, table array, col index number, [range lookup], FALSE COMMAND '=vlookup(lookup Values, table array, col index number, [range lookup]

For examples please check the below images

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 40 (Using FORECAST Command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to forecast any value , then we can use a command “ FORECAST”.  It is help us in preparing Budget, Setting Targets etc.

What Does It Do ?

This function uses two sets of values to predict a single value. The predicted value is based on the relationship between the two original sets of values.

The way in which the prediction is calculated is based upon the assumption of a Linear Trend.

How to use it ?

'=FORECAST(Item To Forecast , Range Y ,Range X)

·         Item To Forecast is the point in the future, (or past), for which you need the forecast.

·         Range Y is the list of values which contain the historical data to be used as the basis of the forecast, such as Sales figures in our Example given below.

·         Range X is the intervals used when recording the historical data, such as Month number in our Example given below.

For examples please check the below images

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 39 (Calculating Depreciation with DB Command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If Ever we want to calculate depreciation based on SLM Method i.e. Straight line method , then use “ DB” command.

What Does It Do ?

This function calculates deprecation based upon a SLM Method i.e. Straight line method.

How to use it ?

'=DB(Purchase Price, Salvage Value , Life , Period To Calculate , First Year Month)

The First Year Month is the month in which the item was purchased during the first financial year. This is an optional value, if it not used the function will assume 12 as the value.

For examples please check the below images

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 38 (Using Convert Command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to covert any values in measures , then we can use “CONVERT” Command.

What Does It Do ?

This function converts a value measure in one type of unit, to the same value expressed in a different type of unit, such as Inches to Centimeters.

How to use it ?

''=CONVERT(Amount To Convert , Unit To Convert From , Unit To Convert To)

For examples please check the below images

Bonus Tip : Certain Abbreviation that can be used to use the Command. Please find the attached Images for the Abbreviation.

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 37 (Using COUNTA Command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to count the cell in which text or number is written , then we can use “COUNTA” Command.

What Does It Do ?

This function counts the number of numeric or text entries in a list. It will ignore blanks.

The =COUNTA() function has been used because of its ability to count text and numeric entries.

How to use it ?

'=COUNTA(Range1,Range2,Range3... through to Range30)

For examples please check the below images

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456

Excel Tips - Tip 36 (Calculating Factorial using FACT Command)

Hello,

Hope my excel tips are helping you out.

Here is Today’s tip for you. Hope this help you.

If ever we want to calculate the factorial of any number, we can easily calculate the same by using “FACT” Command.

What Does It Do ?

This function calculates the factorial of a number.

Factorial is calculated as 1*2*3*4..etc.

How to use it ?

'=FACT(Number)

For examples please check the below images

Bonus Tip : Decimal fractions of the number are ignored.

Hope the Tip is found interesting to you and the same is clear.

Looking forward for a feedback.

If any of your friend is also interested for the said excel Tips , then asked them to fill the form by clicking on the below mentioned link:

https://docs.google.com/forms/d/12T8v5XY0XlF9IhuCZPQpydd8hjCtrW7hZs9_GiOffIw/viewform?c=0&w&usp

If you have something that you want to share with the group, please write to excelms.tips@gmail.com

Thanks & Regards,

CA Mohit Bansal

Excel Enthusiast

+91-9045773456