Using the COLUMN formula to create a draggable VLOOKUP formula

The following is the syntax of the vlookup formula:

I have seen most people using a hardcoded number in the third argument of the formula. However, the downside of this is that if you drag the formula to the right, you will have change the number manually.

This is where a handy formula COLUMN comes into play. 

The COLUMN formula returns the number of the column added as the argument in the formula. Therefore, let's say your array table in the VLOOKUP formula is starting from column A, and your column index is D, then you would enter 4 as your argument for column index. However, you can easily use COLUMN(D1) and this will return 4 as well. Furthermore, of you drag it across, it will change to COLUMN(E1) which will return 5.

If your table starts from column B, you can subtract 1 from the COLUMN formula so that it returns the correct column number. However, let's say your table is starting from column AL and your column index is BX, instead of counting manually the column number or what you want to subtract from the COLUMN formula, you can always just use COLUMN(BX)-COLUMN(AL)-1. This will give you the exact column number which you want and return the correct figure.

Hoping this tip will save you a few seconds of your time counting columns and manually changing the column numbers. Have a great Sunday!

The IF formula

This is a very short post showing the syntax of the IF formula in excel. The IF formula is basically used to see if a condition is met and returns a result depending on whether the condition has been met or not.

The syntax is as follows:

=IF(logical_test,[value_if_true],[value_if_false])

As you can see the function takes three arguments, out of which two are optional. 

The first argument is the logical test. Here you provide the condition This can for example be:

A1>5 (The value of cell A1 is greater than 5)

The second argument is the result to provide if the condition is met. Let's say the A1 is 9. Now if the value in A1 is greater than 5, you want to subtract 5 from it. There the second argument will be:

A1-5

The third argument is the result to provide if the condition is not met. In this case if the vaule is not greater than 5, you want to take the value from A1 as it is. Therefore the third argument will be:

A1

Combining all this into one function gives:

=IF(A1>5,A1-5,A1)

(I read it aloud as If A1 is greater than five, subract 5 from A1 else return A1.)

Now remember the last two arguments as optional, however it wise to always enter the arguments as you might not get the result you expect if you miss out one of the arguments. Excel will give you an error if you only provide the condition. If you simply want to return a true of false, you can omit the IF function completely and just type the following in the formula bar:

=A1>5

This will return a true or false depending on the whether the condition is met. The same can be achieved using the IF function by entering TRUE or FALSE as the last two arguments.

You can't do away with the second argument because then what you are keeping as the third argument will be the second argument for excel. Putting nothing in between the commas as the second argument will return 0 if the condition is met. If you want to return a blank if the condition is true, you simply put in:

=IF(A1>5,"",A1)

You can completely do away with the third condition i.e. you might not keep the second comma at all. In this case if the condition is not met, you get the result as FALSE.

To avoid complexities, it is better to enter all the three arguments. A very simple and basic formula to use once you get hang of it. 

Only show the result when input is entered

There are times where you may have column set up with formulas so that all the columns gets calculated when the inputs are entered.

Take the example of the below table:

Column C, D, E, and F are all filled with formulas so that the columns get calculated once the net price is entered.

However, what if you want to show any kind of output only when the net price is entered. You can add a simple formula to check if the net price has been entered, any only calculate the gross price if output has been entered otherwise show a blank. I am sure you know where I am getting to.

The formula to use is the IF formula.

 IF (INPUT IS NOT BLANK, YOUR FORMULA HERE, EMPTY STRING)

 In C3, the following can be entered:

 =IF(B3<>"",B3*3%,"") 

This can be repeated along the columns and the formulas can then be dragged down, providing a much cleaner work sheet:

Changing Cases in Excel

A very small post explaining some simple text functions for manipulating text cases in excel. You may have received raw data in a case which you would like to change so that it looks better to eyes.

There are three formulas which you can use for this purpose:

1. =UPPER()
Changes the whole text to upper case.

2. =LOWER()
Changes the whole text to lowencase.

3. =PROPER()
Changes the first letter of all the words to upper case and the rest to lower case.

The syntax of all the three formula is simple: Enter the cell containing the text that you want to change in the brackets.

The screen shot below shows the use of the three formulas:

Show Formulas

It's very rare to find an effective excel sheet without formulas. However you will only see the output of the formulas in the cell, unless you click on that specific cell. You can use the 'Show Formula'  

Here is an example of a sheet I just created: 

This is how the sheet looks when I select 'Show formula':

When you select a cell with a formula, you will notice that it highlights all the cells that the formula depends on:

This function is very useful when you are reviewing an excel worksheet. It can be used to find any errors or manipulations that may exist in the sheet, which would otherwise be very difficult to discover. Take a look at the next screenshot:


In the above, you might not discover the 'minor' errors and adjustments made to the formulas by just looking at the sheet, but it does contain them, and they are not minor. This is what the work sheet looks like when I click 'show formulas.'

You will immediately notice the various errors and manipulations that have been made in the worksheet. Some of values in the price column are not even formulas, but just numbers, there have been minor additions made to the formulas, the wrong VAT rate has been used and formulas referencing wrong cells have been used. It may even be difficult to notice that the SUM function have not be input correctly unless you select the cell and see that the first cell have been left out.

Now just imagine what would happen if you give such an invoice (forget above how an actual invoice should look like, just the calculations) and that customer discovers the errors at some point (though, I highly doubt they would really mind). In fact, in one of the future posts, I will show how to make an invoice that is at least not easily to manipulate.

Sheets with minimum data and simple formulas like the one shown in the example above can be easy to audit even without the use of show formulas, however it can be harder to audit it when larger sheets with more data and complex formulas are involved.

I guess that this post is becoming longer than I planned to because of the screen shots, so that's all for now. Be sure to drop any questions or comments below.

Random Numbers

Ms excel provides a very easy way in which you can generate random numbers. There are two formulas which can be used. 

1. RAND 

This formula generates a random number between 0 and 1. 

As you can see above, the formula is very easy to use. All you need to do is to select the formula and press enter, and it will give you a random number. 

It might not sound very useful, but if you consider that it can generate the random number to as many as 15 decimal places, it can help you in generating as many random numbers you want. To increase and decrease the decimal places, simply click on these two: 

2. RANDBETWEEN 

 This formula generates a random number between 2 specific numbers. 

=RANDBETWEEN(bottom,top) 

The above is what you get when you select the formula. 

So if you want a random number between 1 to 10, then you input the formula as: =RANDBETWEEN(1,10) 

Note that both the numbers are inclusive. 

Also you will only get whole numbers as shown in the example above. If there is a way to get it in decimals, I am yet to find out how. 

I find RANDBETWEEN much more useful than the RAND formula since if I want to select a particular random item from a numbered list than I just have to choose the bottom and top numbers and generate the random item, as opposed to using the RAND function and getting values that are way out of range. However, the RAND function becomes very useful when you want to sort a list randomly. If you don't yet know how, you can always experiment, but I will post a detailed example of this in the future. 

 One very important thing to note about these two formulas is that the values will keep on changing every time you make a change in the workbook or even if you just close it and open it again. So how do you make sure you that after you get your random value, you keep that value and have proof that you used random numbers to generate it and not just kept a number that came to your mind? Since I want to keep my posts small and simple, I will show you an example of how to do this after a couple of other posts which shows how to use some other excel features which you will need to know to accomplish the above. 

Posts you can expect in future (you are only able to see this because I have not posted them yet):

•  Using the RAND function to randomly sort lists. 
• Documenting that you have actually used the random numbers formula to pick out the random item.

 If you have any question or comment, just drop it below and I will get back to you as soon as I can.