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:

Printing Webpages to PDF Using Google Chrome

I have had many times where I want to save an article from the web so that I can go through later.. or just store it for future use, even though such a future may never come.

Google chrome provides a neat feature where you can actually directly print the web page to PDF.

You use the normal print command (ctrl + p) and then change the printer destination.


One you have reached that stage, you select the destination to Save as PDF and then Save the document. You will then be told to select the location to save the file and then you are done!

 A good way to archive articles you have enjoyed!

Variables

All programming languages (that I know of) have variables. We assign values to variables, so that we can refer to that value by using the variable anywhere in the program. We define a variable using '=' sign. For example:

 a = 2
 

This means that the value 2 has been assigned to the variable. Anywhere from this point on, we use 'a', we are essentially using '2'. For example, the following code will output '2':

 print (a)
 

Output:

 2
 

In the Hello Python! post, instead of using "Hello Python" as a string, we could have used the following:

 greeting = "Hello"
 greetingTo = "Python"

 print (greeting + ", " + greetingTo +"!")
  

Output:

 Hello, Python!
  

In this way, we can change the greeting and the person we are greeting to by changing it in the variable name. For example:

 greeting = "Good morning"
 greetingTo = "Reader"

 print (greeting + ", " + greetingTo +"!")
 

Output:

 Good morning, Reader!

The comma and exclamation mark are hardcoded in the print statement as they don't really need to change.
More about variable names, types and use of the '+' operator in the future posts.

"Hello Python!"

Today I am proud to say that I have learnt a few basics of python. By now, I have written quite a few simple codes and am still hoping to write more. I have published a few codes in a site called checkio.org, which is an awesome site to code by the way.
To use python, you must download python, however you can find a lot of online compilers as well.
So as a greeting to python, let's write a very simple code. To output 'Hello Python', we use the following:

 print ("Hello Python"!)
  

That produces the output:

 Hello Python!
  

The output that we require is kept in between the paranthesis. Please note that the paranthesis are very important. If we don't keep these we get a syntax error:

 SyntaxError: Missing parentheses in call to 'print'. 
  

More on variables in the next post.

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: