I'm doing a task for an Assignment in Microsoft Excel.

The material isn't overly helpful and nor is there teachers lol......


The biggest problem I am facing now is working with the VLOOKUP function and what information they want.

I have been asked to use the VLOOKUP function, to the best of my understanding to produce a percentage for calculating a discount on a fee for materials based on a commencement date and paid date.

So for example, a person's material commences on 9th of september (they display it in excel as 9 Sep which excel converts to a formatted date) and they paid on 1 Sep, if there is less than a 7 day difference then there is no discount, if greater than 14 5%, if greater than 22 than 8% etc.

So basically the earlier they pay, the bigger discount they recieve.

But the VLOOKUP examples they provide just don't make sense to me, like this one:

=VLOOKUP(B5,$A$13:$B$19,2)

But they are only working with 1 cell, I have a commencement and paid date that need to be worked with, how can I fit this in?

Basically I need to find out how many days difference there is between commencement and paid dates, and refer to the lookup table to get a discount percentage from the second column.

Any help is greatly appreciated, I have been stuck with this task for quite awhile .

 

 

 

Reply

I have done something similar with income taxes, but the basic principles are the same, so it should work. Using the function you gave earlier

=VLOOKUP(B5$A$14:$B$19,2)

this would be how it would work

in the location B5 is your lookup value, so in other words, how many days before the material commences did they pay (5 days, 6 days etc)
in the location A14 to B19 you will have a two column selection, which in one column would have the days (14, 22 etc) and the other column would have the percentages (0%, 5%, 8% etc).

The 2 in this case is the column that will be used to give the result, in your case would be 2, because the percentages are in the 2nd column.

I have attached a simple version of what I have explained here for you to have a look at.

Hopefully this is helpful, if you need anything else explained, please ask!
-jimmy

Reply

That would be fine, but the problem remains of how to extract the difference in days between commencement date and start date cells, as they vary for each persons entry.

Reply

Hi Chesso,

It's not very clear from your description what your objective is, but based on a pure guess I would say you need to convert the two columns you are working with into one column by either:
- concatenating the two dates into something like 2007013120070731
- computing the difference in days between the two dates

Once you have reached that stage you need to apply the VLOOKUP function correctly. I will show you how to do that because most people I have worked with are confused on how to use this function correctly.

Here is a simple example setup of an Excel file along with instructions on how to make the column selections when you use VLOOKUP:





After you clicked inside the Table_array text box, you need to select both coulumns A and B by first clicking the column header A, keeping the mouse button down, then moving the cursor over column B and releasing the mouse button.
I find that it's here that most people get confused.
Also, make sure that Range_lookup = FALSE, otherwise you get unpredictable results.

 

 

 

Reply

that shouldn't be too hard! because excel is american, you have to go by their conventions, so by that todays date is actually the 7/29/07 where we would say its the 29/7/07.

So, if you put the 'commencement day' in column A, cell 2 and the 'pay day' in column B cell 2, then go A2-B2, you should get your difference in days.

The larger day has to always be first, otherwise you are going to end up with negative values, which would mean they payed after the course started!

Attached is a modified copy of the previous file, with paying and commencement days to work out discount percentages!
-jimmy

Reply

For computing the difference in days between two dates, you can use the Excel function DATEDIF, one of the tutorials that shows you how is "Calculating an age between two dates using the DATEDIF function":

http://www.meadinkent.co.uk/xl_birthday.htm

Reply

I tried the DATEDIF function in excel 2007 and it didn't work, not sure if they don't use it anymore in the new version or if i was entering it wrong!

Reply

Thanks dserban but I rarely use wizards, I'm a programmer so am used to writing out functions in a more direct format.

Thanks, I'll test out a few things with the dates and see if it works, I tried incorporating like A2-B2 into the actual VLOOKUP but I got like 0.05 and things like that, instead of 0%, 5% etc.

DATEDIF using "d" as formatter works, but it still gives me like 0.05 for some reason.

Reply

if you got 0.05 that means that you got the function right, but because you have the cell format set as a percentage value, as 5% is actually 0.05 thats what will show up in a cell thats not 'percentage' formated! Format the cell that is showing 0.05 and you will see percentage values!

The file I attached in post #5 should be working properly if you need a reference!
-jimmy

Reply