Computer Gyan

Friday, 11 December 2020

Excel Convert Data English to Hindi

Why we need to change the version or languages? Very simple because of your Boss’s instruction and nowadays Excel is being used in Indian Local Market so sometimes it is necessary to provide reports in a convenient language (Hindi in India). As you know the MS-office does not provide the option to write in Hindi or any other languages. So where do we go let’s read the articles carefully.

First of all we need to open Google Sheet in our browser.

We have to log into our Google Account.

and click on Blank

After that Copy Data in English from your source Excel sheet & paste it in Google Sheet and Apply Formula

=GOOGLETRANSLATE(C3,"EN","HI")

Here in my file - 'C3' is source data cell.

- 'EN' is from language. (Type EN for English)

- 'HI' is destination language (Type HI for Hindi)

Press Enter and you will get a result.

Copy All Hindi Data From Google Sheet and Paste it in Your Excel File.

For more Visit My Blog

Thank You

Friday, 4 December 2020

Convert an Amount from Numerics to Word (Indian Rupees) in Excel 2010

"NumtoRupees" Indian Rupees Function is an Excel function manually created with VBA code to change a given Amount to written words in Indian Rupees along with paise. In other words it converts "₹" in to "INR".

This function is not a inbuilt function to display the numeric value in words.

So we are going to create this function.

First of all you have to click on this LINK this will open a new tab containing a file see in picture below.

1. Click on file and Press Ctrl+A (select all) and Copy All by pressing Ctrl+C.

2. Go to Desktop -----> Right Click on Desktop -----> New -----> Microsoft Excel Worksheet. (See in Picture below)

See there is a file on Desktop, Give a Name to this file and open it.

3. After open this file press Alt+F11 for open VB File window and go to Insert ---->Module.

4. In this next Module code file Paste code by pressing Ctrl+V.

5. After doing step no. 4 press Ctrl+S for save Module.

This is will be save as a Macro enabled workbook so click on YES for continue. (See Picture Below)

6. Save excel file. and Click on OK.

Congratulations !!

Your File is ready to use..

Ok Let's use this file with in example.

--> Create a column containing some numbers from B3 to B24.

--> use formula in cell C3 =NumtoRupees(B3).

And Press ENTER.

See the formula is working. 👇

Thanks for Learn with me.

Tuesday, 1 December 2020

How to Upload files in Google Drive in Mobile Device.

Are you worry about your files security...?

Nowadays, users are worried about the security of files in their mobiles, for which they use some third party applications, but it is not right to rely on these applications. Cloud storage is considered the best option for this nowadays. So today we will talk about one such cloud storage "Google Drive", How to securely upload your files in Google Drive..

Let's understand this with the help of pictures.

Steps for create a new folder and upload a file in it..

Step 1

Open "Google drive" from menu.

Step 2

Tap on "+" sign botton right side on screen.

Step 3

Select "Folder" option from menu.

Step 4

Give a name to your folder & tap on "Create" option.

Your folder has been created on screen as shown in picture below

Tap on folder to upload files in this folder and repeat Step 2 Again.

Table of Figure

👉 Step 1

👉 Step 2

👉 Step 3

👉 Step 4

👉 Result

---------------------------------------*************------------------------------------

Saturday, 28 November 2020

What is Monthly Average Balance (MAB) in banking system and how can we calculate in M S Excel.

Monthly Average Balance (MAB) confuses many account holders and they wrongly take it as maintenance of a specified amount in their Savings Bank Account all the time.

However, MAB is actually calculated by adding up all end of day balances in an account in a month, and then dividing it by the number of days in that particular month. While calculating MAB, all bank holidays and working days are included too.

Suppose the “Monthly Average Balance” requirement of your bank is ₹ 10,000. It implies that the bank wants the average in your account to be ₹ 10,000. It does not means that you should have ₹ 10,000 balance at the end of every day. It’s your discretion as to how to go about it. You can maintain a balance of ₹ 10,000 every day or ₹ 3,00,000 (₹ 10,000*30) on any one day of the month which have 30 days in it or ₹ 3,10,000 (₹ 10,000*31) on any one day of the month which have 31 days in the month. Ultimately the average needs to come around ₹ 10,000.

So let's calculate it.

First of all we need bank statement as in picture below.

Here we can see Date, Deposit Amount, Withdraw Amount & Balance Amount & we are going to calculate MAB from these data.

On April 1, the balance in the account is ₹ 35,000.
On April 8, withdrawal of ₹ 20,000 takes place. EOD balance is ₹ 15,000.
On April 16, deposit of ₹ 12,000 & withdraw of ₹ 10,000 takes place. EOD balance is ₹ 17,000.
On April 22, withdraw of ₹ 15,000 takes place. EOD balance is ₹ 2,000.
On April 27, deposit of ₹ 1,000 takes place. EOD balance is ₹ 3,000.

Summing up the EOD balances as shown in Picture above:

From April 1 to April 7 (7 Days), sum of EOD balance will be ₹ (35,000*7) = ₹ 245,000.
From April 8 to April 15 (8 Days), sum of EOD balances will be ₹ (15,000*8) = ₹ 120,000
From April 16 to April 21 (6 Days), sum of EOD balances will be ₹ (17,000*6) = ₹ 102,000
From April 22 to April 26 (5 Days), sum of EOD balances will be ₹ (2,000*5) = ₹ 10,000
From April 27 to April 30 (4 Days), sum of EOD balances will be ₹ (3,000*4) = ₹ 12,000

Sum of all the EOD closing balances = 489,000

No. of days in April Month = 30 Days

MAB = (Sum of all the EOD closing balances) / (number of days in a month)

So, the Monthly Average Balance will be 489,000/30 i.e. 16,300.

Now if we want to calculate MAB in M S Excel by using formula the stay with me..

First of all we need bank statement in excel format as in picture below.

now create a new table for MAB calculation by using some formulas shown in picture below.

For above calculation check formula description in below table

For more Information Follow my blog.

Thank You.

Monday, 23 November 2020

Print bank details in Invoice in Tally ERP 9

In today's new modern business era, credit transactions are paid through internet banking. In order to save the customer time in this process of payment, the company prints its bank information in the invoice.

So let's start "How to Print Bank Details in In Invoice ?"

Step-1

Create a Bank ledger from.

Gateway of Tally ----> Accounts Info.----> Ledger ----> Create.

and mention all information like Account Holder Name, Account No., IFSC, Bank Branch Name as you can see in picture below.

Step-2

Go to Gateway of Tally ----> Accounting Voucher & Press F8 (Sales Voucher)

Press Alt+P or Click on Print option Top Left on screen.

Step-3

Here you can see a window as given in picture below.

Here press F12 (Configure) .

Step-4

In this configure window do Yes to the option Print Bank Details and select Your Bank Name from the List & Press Ctrl+A (Accept).

As you can see in this invoice your Bank details has been Printed in invoice..

Thank You.

For more Tally Related blog please Click Here.

Tuesday, 17 November 2020

Apply LOGO in Tally ERP9

As we all know that the logo is a printed symbol or design that a company or an organization uses as its special sign to aid and promote their products.

Here we are going to know that how to apply LOGO in Invoice or Reports by using Tally ERP 9.

Steps for printing Logo in Tally...

1. From Gateway of Tally press F11 (Features) & Then press F1 (Accounting Features).

2. Do YES for the option "Enable Company Logo" as you can see in the Picture below.