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

Excel Tips - Tip 35 (Using Macros)

Hello,

Hope my excel tips are helping you out.

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

Macros provide an ideal way to save time on predictable, repetitive tasks as well as standardize document formats – many times without having to write a single line of code.

What Does It Do ?

Macros are incredibly powerful and can do pretty much anything your imagination can conjure. As a (very) short list of functions you can do with a macro:

·         Apply style and formatting.

·         Manipulate data and text.

·         Communicate with data sources (database, text files, etc.).

·         Create entirely new documents.

·         Any combination, in any order, of any of the above.

How to use it ?

We have a sample of data as shown in figure below :

Now we need to create a macros for the repetitive command that we need to use.

To create a macro, go to View > Macros > Record Macro. As shown in the below image. From there we need to select Record Macros.

As we click on record macros a new dialog box will appear as shown in images.

As soon as you click OK, our Macros command start working.

Now we have to give command that we need to record it.

Examples of Command that we uses :

·         =SUM(B2:F2)

·         =AVERAGE(B2:F2)

·         =MAX(B2:F2)

·         =MIN(B2:F2)

You can also use other command like formatting ( Bold, Italics etc)

Once the above command used in the one cell, now use Drag and Drop option to copy all the formulas to all the desired cell.

Once we complete all the commands then we have to go to Macros again and click on Stop Recording.

Once the macros stop, then Congratulations – you have just created an Excel macro.

Now Select the all by pressing Ctrl+A and then delete the all text. Now save the file as macros enable as shown in image.

Now close the file. Now open the new file that we have just saved in our PC.

Enable the macros as shown in figures

Click on options and select Enable as shown in images

Now Copy paste the data as shown in figures 1 which is our base DATA file.

Now Run macros as shown in figures

The all command will automatically filled as per our command used while Recording Macros.

That’s how we can use Macros.

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 34 (Using Lookup Formulae)

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 the pick up a particular item from a group of range , then we uses “ LOOKUP” Command.

What Does It Do ?

·         This function looks for a particular  information from a list, and then picks an item from a second range of cells.

·         The What To Look For should be a single item.

·         The Range To Look in can be either horizontal or vertical.

·         The Range To Pick From must have the same number of cells in it as the Range To Look in.

How to use it ?

''=LOOKUP(What To Look For ,Range To Look In ,Range To Pick From)

For example, please check the 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