Converting numbers to words

How do we convert numbers to words. Especially useful if you are writing cheques (checks) through printing through a soft file.

The logic given below is one for India – it uses crores and lacs. The logic works for all numbers between 1 and 99,99,99,999 (100 crore -1)

Say the numbers that need to be converted to words are given in column A

Step 1: Start with defining the following arrays

Step2: Extract units, tens, hundreds, thousands, lacs and crores from the numbers in column A

The result is given Array1 below:

The formulas used are:

For crores: D2=ROUNDDOWN($A2,-7)

For lacs: E2=ROUNDDOWN($A2,-5)-$D2

For thousands: F2=ROUNDDOWN($A2,-3)-SUM($D2:E2)

For hundreds: G2=ROUNDDOWN($A2,-2)-SUM($D2:F2)

For tens & units: H2=$A2-SUM($D2:G2)

Drag till end of columns

Modify the Array1 so that resuts appear as this in Array2:

The formulas used are:

For crores: I2=D2/10^7

For lacs: J2=E2/10^5

For thousands: K2=F2/10^3

For hundreds: L2=G2/10^2

For tens+units: M2=H2

Step 3: Convert numbers to words for each column

The formulas used are:

Crores: N2=IF(LEN(I2)=1,VLOOKUP(I2,Unit_array,2,FALSE),IF(VALUE(LEFT(I2,1))=1,VLOOKUP(I2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(I2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(I2,1)),Unit_array,2,FALSE))))

Lacs: O2=IF(LEN(J2)=1,VLOOKUP(J2,Unit_array,2,FALSE),IF(VALUE(LEFT(J2,1))=1,VLOOKUP(J2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(J2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(J2,1)),Unit_array,2,FALSE))))

Thousands: P2=IF(LEN(K2)=1,VLOOKUP(K2,Unit_array,2,FALSE),IF(VALUE(LEFT(K2,1))=1,VLOOKUP(K2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(K2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(K2,1)),Unit_array,2,FALSE))))

Hundreds: Q2=VLOOKUP(L2,Unit_array,2,FALSE)

Tens&Units: R2=IF(LEN(M2)=1,VLOOKUP(M2,Unit_array,2,FALSE),IF(VALUE(LEFT(M2,1))=1,VLOOKUP(M2,teens,2,FALSE),CONCATENATE(VLOOKUP(VALUE(LEFT(M2,1)),tens,2,FALSE),” “,VLOOKUP(VALUE(RIGHT(M2,1)),Unit_array,2,FALSE))))

Formula for crores, lacs, thousands, tens&units are similar in logic and construct

The logic used is

• If number is of 1 digit (0 to 9)- then lookup value from Unit_array
• If number starts with 1 (10 to 19) – then lookup value from teens
• For all else (20 to 99) – lookup left digit from tens and right digit from Unit_array and concatenate

Formula for hundreds is simple – lookup value from Unit_array as hundreds can only be from 0 to 9

Step 4: Now the fun part – putting it all together

The formula is simply

B2=PROPER(CONCATENATE(IF(I2>0,N2&” crores “,””),IF(J2>0,O2&” lacs “,””),IF(K2>0,P2&” thousand “,””),IF(L2>0,Q2&” hundred “,””),IF(R2>0,R2,””)))

Have added proper to capitalize the first letters of all words in the text string.

Let me know if it helps you 🙂

Adding errors, texts, numbers, blanks in a list

A quick post.

Here is how you can you can count errors, blanks, numbers, texts in a list.

List A2:A11 is defined as Count_list

To count

• Errors       {=SUM(IF(ISERROR(Count_list),1,0))}
• Blanks       {=SUM(IF(ISBLANK(Count_list),1,0))}
• Numbers {=SUM(IF(ISNUMBER(Count_list),1,0))}
• Text           {=SUM(IF(ISTEXT(Count_list),1,0))}

The formulas are entered as array formuals with ctrl+shift+enter

Preparing a RAG report

To track status of a project or tasks, RAG (Red, Amber, Green) report is commonly used. Given below is a sample RAG report as on 04-Jun-09

The following logic is used for creating the report
 

For Open tasks: 

• Red: SCD – today()+1 is -ve
• Amber: SCD – today()+1 is 0
• Green: SCD – today()+1 is +ve

For Closed tasks:

• Red: SCD – ACD+1 is -ve
• Amber: SCD – ACD+1 is 0
• Green: SCD – ACD+1 is +ve

SCD = Scheduled Completion Date

ACD = Actual Completion Date

The following formulas are used
C2=CHOOSE(WEEKDAY(B2),”Sun”,”Mon”,”Tue”,”Wed”,”Thu”,”Fri”,”Sat”)

H2=IF(F2=”Open”,CHOOSE(2+SIGN(E2-TODAY()+1),”Red”,”Amber”,”Green”),CHOOSE(2+SIGN(E2-G2+1),”Red”,”Amber”,”Green”))

Column H is conditionally formatted to colour the cells –  Red, Amber or Green

Choose function Uses index_num to return a value from the list of value arguments has the syntax CHOOSE(index_num,value1,value2,…)

Sign function returns (0,+1,-1) depending on whether the input is 0,+ve or -ve

You could change the formula depending on the logic you want to use for RAG.

Have fun 🙂

Extract and sum vlookup results

multiple vlookup, sum(vlookup), sum vlookup results in a single formula

Consider the problem as below:
The table gives the sales figures for each of the sales person across five products

Say we need to find the total sales for identified salesperson across 3 products – Prod1, Prod2 and Prod5
One way to do this is to carry out vlookup thrice and sum them up. Here is the same solution but with a single vlookup entered as an array formula.

 

 

The formula in cell B10 is {=SUM(VLOOKUP(A10,A3:F7,{2,3,6},FALSE))} entered as an array formula with ctrl+shift+enter

The formula carries out vlookup for each of the col_index_num in the array viz. 2,3 and 6 and then sums the result.

Let me know if it ever speeds up your task 🙂

10 tips for great formatting

Here is a list of 10 formatting tips that would help make your excel worksheet look great

1. Toggle grid – After you have done your analysis, make the gridlines disappear from your worksheet. It gives a much cleaner look and is very eye friendly. Here is how you do it – Add Form toolbar – View > Toolbars > Forms and click on Toggle Grid

 

2. Highlight through conditional formatting – use conditional formatting to add colours to cells that you wish to highlight. It is easier to find something that you are looking for if it is coloured. Here is an example of highlighting errors in an array.
   
   Format all other cells in the array with the format painter brush to highlight all errors in the array

 

3. Add cell borders – if there are certain cells that you wish to highlight, for instance cells with array summations, use cell borders. You would find this in formatting toolbar

 

4. Table grids of different colours – the default colour for all borders is black. Use colours to your advantage and change it from Format > Cells…>Border>Color:

 

5. Maintain consistent format -Unless the analysis specific requires it, you may like to maintain consistent format for numbers –
   √ Have same number of decimal places – use increase/decrease decimal on formatting toolbar
   √ Date in same format – choose a format from Format > Cells > Number > Date. Once chose you could use the format painter brush to apply to all cells with dates in them

 

6. Resize cells – Resize cells according to the content.  Excel can automatically resize cells according to the contents of the cell. In this example, take the cursor on the line between D and E. Double click when a cross hair appears

 

7. Avoid cell merge as far as possible – Resize cells, insert columns but avoid cell merge as they become messy after some time and are difficult to handle

 

8. Wrap text – If there is too much data in a cell, consider wrapping the text. Format > Cells…>Alignment>Wrap text

 

9. Printing with gridlines – Even if toggle grid is on, print of the worksheet may not display the grids. To display grids – File > Print > Preview > Setup… > Sheet > Gridlines

 

10. To hide errors in prints – File > Print > Preview > Setup… > Sheet > Cell errors as: <blank>

Have fun formatting worksheets 🙂

Sum array with errors

How do you sum up an array which is a combination of numbers and errors? I faced this problem when carrying out a vlookup. The results_array is a combination of #N/A error and numbers as below:

On summing up Results_array through the formula

=sum(A2:A16) gives #N/A error.

To overcome this problem I made use of the following formula

A17  {=SUM(IF(ISNUMBER(A2:A16),(A2:A16),0))}

This is entered as an array formula with ctrl+shift+enter

The formula works in the same way as the results given in explanation array, where

B2=IF(ISNUMBER(A2),A2,0) and then summing up in B17

If you wish to count the occurences of numbers in the array A2:16 you could use this formula

A18 {=SUM(IF(ISNUMBER(A2:A16),1,0))}

Entered as an array formula with ctrl+shift+enter

Now some not so useful information 😉

724 incidentally is a sum of squares of 18 and 20. Try this =18^2+20^2
Or here is a function that you may use = SUMX2PY2(18,20)

Have fun!

 

 

Evaluating step functions

This problem is on step functions. Certain utility company charges me in this manner

For the first 25 units of consumption, the rate is 212 per unit, for next 35 units, the rate is 270 per unit and so on.

I have calculated the total amount payable for certain unit of consumption by using sumproduct and offset functions.

The formula to calculate amount payable is entered in C11 for consumption in B11

C11 =SUMPRODUCT(OFFSET($B$1,1,0,MATCH(B11,Cum_unit,1)),OFFSET($B$1,1,1,MATCH(B11,Cum_unit,1)))+(B11-INDEX(Cum_unit,MATCH(B11,Cum_unit,1)))*INDEX(Rate,MATCH(B11,Cum_unit,1))

The formula can be divided in two parts, the part in blue calculates the amount upto the upto the completed values given in cummulative units (Cum_unit) – 100 in the given example. Cost for remaining units (120-100=20) is calculated in the second part (formula given in green)

Cum_unit is D2:D8

sumproduct – sums up the products of two arrays [sumproduct({1,2},{3,4})=1*3+2*4=11]. This is exactly what we need to calculate the first part – {Units}*{Rate}

To find out how much of the array we need to sumproduct, match function is used. Match with match_type 1 is used for array Cum_unit, so that it returns a value equal to or lesser than the lookup_value. In our case it returns the value 3

Offset is used to generate the arrays for carrying out sumproduct, the reference is taken as B1, for Units array, row offset is 1, column offset is 0, height of array is given by the match function above – which is 3

For Rate array, row offset is 1, column offset is 1, height is same as given by match function.

The cost for remaining part is carried out through the use of index function on the two array Cum_unit and Rate and multiplying them

You would find this very useful when working on large units and rates arrays.

As always let me know if you find this useful 🙂

Lookup to the left

For looking up through vlookup, the value to be returned needs to be to the right of lookup_value. Here is a solution for returning a value to the left of lookup_value using match and offset functions

The problem is given in the image sheet below

 

The H1Y1 performance of certain sales people have to be returned. This is achieved through this formula in cell B10

Method 1: Match with Offset

B10 = OFFSET($C$2,MATCH(A10,$C$3:$C$7,0),-2)

This is then dragged to cover all sales people for which figures are required.

Note: match function returns the placement of lookup_value in the column, this is then used to offset. Note that offset reference (C2) is one cell above the lookup column (C3:C7) to ensure that if match returns 1, offset gives a correct value

Method 2: Sum as an array function

B10 {=SUM(($C$3:$C$7=A10)*$A$3:$A$7)}

entered with ctrl+shift+enter

Method 3: Sumproduct

B10=SUMPRODUCT(–($C$3:$C$7=A10),$A$3:$A$7)

May you find this useful!

Handling errors in excel

I have listed down the types of error in excel and their likely causes in the table below:  

 

 Error type can be found out through the formula =Error.Type(cell_ref)

Count instances of error

One of the ways to count the total occurence of error in an area is this

{=SUM(IF(ISERROR(B2:B8),1,0))} entered as an array formula with ctrl+shift+enter

In the above example it returns 7

Replace error with a dash

If a formula gives you an error for certain input parameters and you do not want these to show up on excel sheet, you could use this function

=IF(ISERROR(formula),”-“,formula)

We have used this in creating spaces within a list using indirect function

Let me know if you need any help with these errors. Have fun handling errors 🙂

Cows and Bulls

Here is how you can play the game Cows and Bulls in excel

The objective of the game is to guess the secret number (which is hidden from the player). The player can guess the number as many times. If the matching digits are on their right positions, they are “bulls”, if on different positions, they are “cows”. Example given below

The formula for cows:
C3 {=COUNT(MATCH(MID(B3,ROW($1:$4),1),MID($B$1,ROW($1:$4),1),0))}
(entered as an array formula, ctrl+shift+enter)

The formula for bulls
D3 {=SUM(IF(MID($B$1,ROW($1:$4),1)=MID(B3,ROW($1:$4),1),1,0))}
(entered as an array formula, ctrl+shift+enter)

Drag the formula down. You could keep entering your guesses in column B

One could also highlight the cell if the number is guessed correctly, through conditional formatting
Click B3 and through Format>Conditional Formatting…

Copy the format to all cells in column B through Format Painter brush on the toolbar

Tip: If you are playing the game alone and have no one to give you a 4 digit number, you could use rand() function

In the secret number cell enter = ROUND(RAND()*10^4,0), Press F2 and F9 – this would freeze the random number. Make sure that no digits are repeated and the random number is 4 digits long. You can use len function and check duplicate function for that

Have fun playing the game 🙂