Step by step revision?

[Sid_London]

Hi guys,

I'm going for various entry level jobs in banks and need to increase my Excel skills for a possible test as part of the interview process. This won't include modelling, but I do need to be confident with VLOOKUPS, calculated fields in pivot tables, COUNTIF, SUMIF, SUMPRODUCT and currency conversion (i.e. given list of figures in various currencies and need to add a column with the amounts in GBP based on daily rates).

My background is in accounts so I am quite familiar with Excel spreadsheets and have used them on and off since highschool, but the above are not things that I am brilliant with. I can understand the formula for IF/VLOOKUPS, for example, and can work out what it is doing if I see it already written down. But I need to be confident enough to write it from scratch when needed and understand what all the seperate componants mean. At present, I can only write very basic formula and it takes me a while. I'm guessing they will try to throw me off during the test and ask me trick questions, so I want to cover all eventualities.

Is there a basic revision set I can go through on videos? I know there are several general Excel revision courses online, but they normally start from the very very basics (such as autosum and changing column width).

 

[Sid_London]

Also, I have a specific question about V Lookups:

When converting a list of currencies (laid out as A2:A27 'Item', B2:B27 'Currency type', C2:C27 'Amount in original currency', D2:D27 'Amount in GBP'). In columns G and H I have a short list of all the exchange rates for various currencies I am converting to GBP. Column G lists the types used in column B and column H the exchange rate for that day.

In column D I have formula IF(B2<>"",C2*(VLOOKUP(B2,$G$2:$H$8,2,0)),"")

I am reading this formula as "If cell B2 has less or more than nothing as a value (i.e has anything in it), then multiply the value in cell C2 by (use number found by the method (check if the value in B2 is also in cells G2 to H8. If so then use the value in column 2 of G2:H8 as the number to multiply C2 by. ***) if not then do nothing) The $ obviously means that I can drag the formula into all cells in column D and the B2 part will change accordingly, but the lookup will only check cells G2 to H8 for a match (I probably explained that badly, but I know the $ fixes the cell reference).

I get most of it, but what does the 0 after the 2, represent? Is this just another way of writing ZERO to make sure Excel uses only the EXACT value in B2? Or is it doing something else?

 

[VoG]

That is looking for an exact match. It is (IMO) preferable to use FALSE instead of 0.

 

[Sid_London]

Ah okay, so '0' does the same as typing 'FALSE'? Why would it matter which one you used then?

 

[VoG]

Some good places to start

http://www.contextures.com/tiptech.html

http://www.datapigtechnologies.com/ExcelMain.htm

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

http://www.xlfdic.com/

 

[VoG]

0 does the same as FALSE. Excel expects a Boolean so must have to coerce a number to Boolean.

 

[Sid_London]

Ok, thanks for clearing that up for me. Much appreciated!

And I will check out those links.