Amount in words

Amount in words

Microsoft excel, does not by default, offers a function to convert a numerical value into text. However, it allows you to do so using its visual basic editor.

Open the excel workbook in which you intend to use the functionality. Press   Alt+F11  to open the Visual Basic Editor. Click  Insert  >  Module  (a new window will open). Copy and paste the code. Save and close the visual basic editor and then save the excel workbook in Excel macro-enabled format.

Now open the excel workbook and use the function as    =spellnumber(Cell reference)   

 If you are familiar with VB code, you can modify the code according to your own needs. For example, if your local currency is other than dollar i.e “Rupees” with sub unit “Paisas” you can find and replace “Dollars” and “Cents” with your relevant currency units.

Remember that this would work only in the excel workbook in which you have inserted the module.

 To download the VB code referred to in this post and the template files, please visit: https://sites.google.com/site/officetemplatesource/news/untitledpost

Precision as displayed

Precision as displayed

Excel stores numeric data as " Double Precision Floating Point" which is a design requirement but can result inaccuracies in certain exceptional cases.

Consider you have the expression  =103/7 in one cell and =99/6 in the other. Number that excel would be storing as the answer would be  14.7142857142857000...... for first cell and 16.500000000...... for the second cell.

Consider you have selected to display all amounts as number format with no decimal places. The amount would be appearing as 15 and 17 now if you add both cells in another cell, excel would give the answer as 31 which is correct mathematically but not if someone performs casting by simply adding the visible numbers i.e 15+17 = 32. This problem arises frequently while making financial statements where amounts are rounded to thousands or millions.

One solution to this problem is to round the source amounts down to zero decimal places using the round function. The other option is the use of "Precision as displayed" feature. To use it, simply go to Office button >  Excel options > Advanced > When calculating this workbook > Set precision as displayed.

For Excel 2003 and earlier versions, go to Tool > Options > Calculation > workbook options > Precision as displayed.

However there is one problem i.e the changes made by this setting cannot be undone, so preparing a copy of the document may be considered.

Secondary Axis on Charts in Microsoft Office

Secondary Axis on Charts in Microsoft Office

If you are preparing a chart using two data fields that are so different in terms of data (i.e one data in numbers and the other in percentages ) they cannot be displayed on a single scale. For example:

Year        Population           Growth
2005     150,000,000             2.4%
2006     153,900,000             2.6%
2007     158,363,100             2.9%
2008     163,272,356             3.1%
2009     168,170,527             3.0%
2010     172,374,790             2.5%
2011     176,339,410             2.3%

without secondary axis

with secondary axis

To make a chart with secondary axis, first insert a chart using the chart wizard in the "Insert" menu (select a 2D chart).After you are finished you chart should appear as in picture-1 above.

To add secondary axis, right click on any data bar shown in the chart window. Then click "Format Data Series"  >  Series Options  >  Plot Series On  >  Secondary axis.

As a result of above, both data series population and growth %age will appear. Now you can decide which data series to show on primary or secondary axis simply by right using the format data series option as explained above.

You can use two different chart types for both axis for better presentation. In the above example we have used line graph for growth %age. To do that simply select the data bar (red) and go to  "Chart tools"  >  Design  >  Change Chart Type  >  Select any line graph.

Generating Random Numbers

Generating Random Numbers

To generate random number in excel use following function.

=rand()
(This will create random decimal numbers each time the cell is refreshed)

If a specific range is required, the above formula can be amended as:


=rand()*(100-1)
(This will create random +ve decimal numbers between 1 and 100 each time the cell is refreshed. If -ve numbers are required the last part of the expression should be written as (1-100)

If only random integers are required, use the INT functions with the above formula as:

=INT(rand()*(100-1))