Help with a vlookup formula

[silk]

I need to produce a template based on someone else’s instructions and I’m really unsure about how to do this.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
What I need to do is make a template that has a VLOOKUP table for a discount on fees. The discount is offered on no. of days paid in advance.<o></o>
With the VLOOKUP table I’ll need two columns. One is a no of days range eg - :<o></o>
0-7<o></o>
8-14<o></o>
<o></o>
The second column will be a percentage discount. eg - :<o></o>
5%<o></o>
7%<o></o>
<o></o>I’ll need a correct formula for Days in Advance<o></o>
A correct formula for Discount<o></o>
And a correct. formula for Fees Paid<o></o>
<o></o>I am a real novice with Excel.<o> </o>Could someone please help.<o></o>
<o></o>I think I've attached what I’ve done so far, based on the information I had I’ve simply entered the columns and data and added the two extra columns, the Days difference and so far empty Fee paid ones<o></o>
I only want to do it the way I’ve mentioned. <o></o>I hop the mess below means I've attached a copy of it. attached a copy of it
<o></o>
thanks

<o>Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 124px"><COL style="WIDTH: 103px"><COL style="WIDTH: 173px"><COL style="WIDTH: 145px"><COL style="WIDTH: 131px"><COL style="WIDTH: 131px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Resort</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Name</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Commencement Date</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Date Paid</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">No. Days Difference</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Normal Fee</TD><TD style="FONT-WEIGHT: bold">Fee Paid</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: left">Tweed Heads</TD><TD>B & A Kelleher</TD><TD style="TEXT-ALIGN: right">09-Sep</TD><TD style="TEXT-ALIGN: right">01-Sep</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">$395.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: left">Daylseford</TD><TD>WG King</TD><TD style="TEXT-ALIGN: right">12-Sep</TD><TD style="TEXT-ALIGN: right">11-Sep</TD><TD> </TD><TD style="TEXT-ALIGN: right">$250.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: left">Surfers Paradise</TD><TD>R & G Smith</TD><TD style="TEXT-ALIGN: right">15-Sep</TD><TD style="TEXT-ALIGN: right">31-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$300.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: left">Daylesford</TD><TD>S Thompson</TD><TD style="TEXT-ALIGN: right">01-Sep</TD><TD style="TEXT-ALIGN: right">05-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$690.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: left">Melbourne</TD><TD>W & E Tindall</TD><TD style="TEXT-ALIGN: right">09-Sep</TD><TD style="TEXT-ALIGN: right">31-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$395.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: left">Broome</TD><TD>F & O Martinez</TD><TD style="TEXT-ALIGN: right">15-Sep</TD><TD style="TEXT-ALIGN: right">20-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$300.00</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>Days Paid In Advance</TD><TD>Discount </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">0%</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">7</TD><TD style="TEXT-ALIGN: right">5%</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">14</TD><TD style="TEXT-ALIGN: right">8%</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">21</TD><TD style="TEXT-ALIGN: right">10%</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E2</TD><TD>=C2-D2</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4 </o>
<o></o>

 

[dabbler68]

Hey silk,
You don't really have to use a VLOOKUP, although I see why you would want to (but it's not necessary). The formula in E2 is fine, so you can just copy it down.

You can use a formula in G2 such as

=F2-(IF(E2>21,10%,IF(E2>14,8%,IF(E2>7,5%,0%)))*F2)

but to replace the VLOOKUP at point to the table, you could also use this

=F2-(IF(E2>$E$17,$F$17,IF(E2>$E$16,$F$16,IF(E2>$E$15,$F$15,$F$14)))*F2)

and copy down as needed

Let us know how it goes, and HTH's

EDIT
To test, I also included this formula into H2, format as %

=(F2-G2)/F2

 

[taigovinda]

Welcome to the board!

Your table looks to be set up correctly as is. The formula you are looking for to get discount % would look like this:

=vlookup(e2,$e$14:$f$17,2,1)

then if you want to take the discount out at the same time, it is:

=f2-f2*vlookup(e2,$e$14:$f$17,2,1)

FYI, the key here is the last term of the vlookup(), the 1. If it were zero, you'd be searching for an exact match (8 days in the first example), would not find it and would return an error. The 1 looks for closest match, and presumes that the table key (E14 thru E17) is sorted in ascending order...

 

[dabbler68]

Hey,
Just curious, but I didn't go with the VLOOKUP because when it's used, and when the payment is made 7 days in advance, it applies a 5% discount, which I thought was incorrect as the OP stated 0-7 days (0%?), 8-14 days (5%)

I'm wondering whether I'm reading that correctly, or perhaps just going mad

If I am (reading correctly that is lol), should the table in E14:E17 then be 0, 8, 15, 22 for the VLOOKUP to work

 

[taigovinda]

Good point, the table is probably not quite right...

Excel will pick the # of days that is less than or equal to the number of days you are looking for. So when you look for 8, it will find 7 days; look for 20 days and it finds 14. If you look for a number that is less than any of the key #s, Excel would return an error, so you should put some negative days in case the payment is late. Maybe more like this, or at least this should be illustrative of the values needed for the table:

Excel Workbook
	E	F	G	H	I	J
13	Days	Discount	-	-	Difference	calced discount
14	-999	0%	-	-	-1	0%
15	0	5%	-	-	0	5%
16	7	8%	-	-	1	5%
17	14	10%	-	-	2	5%
18	-	-	-	-	7	8%
19	-	-	-	-	8	8%
20	-	-	-	-	13	8%
21	-	-	-	-	14	10%
22	-	-	-	-	15	10%
23	-	-	-	-	23	10%
Sheet1

Cell Formulas
Range	Formula
J14	=VLOOKUP(I14,$E$14:$F$17,2,1)

 

[dabbler68]

Well that was my point really...
Because it looks at the data in that way, it applies the discount within the inclusive range of 0-6, 7-13 etc (ie 5% is applied at day 7) instead of the way the OP asked for it to be set as 0-7 (0%), 8-14 (5%) etc

....as I said tho, that's just how I'm reading it, and I could well be wrong

 

[silk]

Well that was my point really...
Because it looks at the data in that way, it applies the discount within the inclusive range of 0-6, 7-13 etc (ie 5% is applied at day 7) instead of the way the OP asked for it to be set as 0-7 (0%), 8-14 (5%) etc

....as I said tho, that's just how I'm reading it, and I could well be wrong

It's Silk here who started this thread.

I've read the comments about the percentage discount rates, and I'm not surprised that there is debate over it because I had some trouble with the lady I talked to about the Vlookup table.
These are the discount rates - :
less than 7 days 0%
7-13 days 5%
14-20 days 8%
21 days + 10%

if the VLOOKUP table (and formulas) should look different could you please advise (I'm doing this from an internet cafe and don't have the tiime to explore it too deeply)

 

[dabbler68]

Hey Silk,
We all love to get to the root of the problem, and sometimes it's hard to all come to the same conclusions as you mention

In answer to your question, I would pay attention to taigovinda's first reply as that will give you the answers that you need, in the way you just mentioned

As a backup though, and as I mentioned in a previous post, if you are unsure and need to test the data, you may want to adopt the following.....if only to test it first

To test, I also included this formula into H2, format as %

=(F2-G2)/F2

Glad you got the result you need, and HTH's

PS, Welcome to the board

 

[silk]

Hey Silk,
We all love to get to the root of the problem, and sometimes it's hard to all come to the same conclusions as you mention

In answer to your question, I would pay attention to taigovinda's first reply as that will give you the answers that you need, in the way you just mentioned

As a backup though, and as I mentioned in a previous post, if you are unsure and need to test the data, you may want to adopt the following

To test, I also included this formula into H2, format as %

=(F2-G2)/F2



Glad you got the result you need, and HTH's

PS, Welcome to the board

By the way, thanks I appreciate the help you guys give

 

[silk]

Hey Silk,
We all love to get to the root of the problem, and sometimes it's hard to all come to the same conclusions as you mention

In answer to your question, I would pay attention to taigovinda's first reply as that will give you the answers that you need, in the way you just mentioned

As a backup though, and as I mentioned in a previous post, if you are unsure and need to test the data, you may want to adopt the following.....if only to test it first

To test, I also included this formula into H2, format as %

=(F2-G2)/F2

Glad you got the result you need, and HTH's

PS, Welcome to the board

While my fascination with Taigovinda's post is endless, I just wish it didn't look like it was written in an ancient Chinese dialect.
Well, to me anyway.
I'm certainly interested in that post, but I just wish I could put it to more use.
I am really hopeless with this stuff. Like I said I've rushed into an internet cafe and will rush back out. I will try to get more involved with this later.
Could someone just tell me if the VLookup table looks right. When I double-checked it against some of the earlier posts it suddenly didn't look too bad.