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,
taigovindas post is spot on for what you need, and don't put yourself down on the understanding, it took me at least 15 posts to work out how to put code in the tags....seriously, we are ALL learning!

All you have to do is keep the formula you have in E2 (and paste down as necessary), and copy taigovindas second formula (the second code box in his/her first response to you), into G2, and copy and paste down as far as you need to go

The lookup table will work perfectly with taigovindas formula above (so no changes needed), but I would recommend you testing it with each variable as per my last post (to make sure you produce the results you are expected to)

Just remember that if you do have any problems, reply again to this post, and we (tai and I) get an email to let us know of the replies, in addition to the normal posting

Take care, and if you have trouble, we hope to see you back here

 

[taigovinda]

Yes Silk, first I would echo dabbler's comments that we're all thrilled to be on this board and try to help/get help and all that ...to recap

-use the second formula in post #3
-your lookup table is perfect EXCEPT where you have 0 days, 0%, I would recommend changing 0 days to -999 days. Otherwise the formula will give you an error value when the payment is late (and the number of days early in column E is less than 0).

Cheers!
Tai

 

[silk]

Quick note, I've got two minutes left in an internet cafe.

The formulas i've tried so far haven't worked.

There has to be three formulas anyway.

1) Was the no. of days difference which I did

2) the VLOOKUP is the discount (in dollars I'd say)

3) should just be the real total paid

 

[taigovinda]

What are you trying? Did you try the formulas below (quoted directly from post #3)? In what way are they not working?

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

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

OR (presuming formula 2 went into cell g2):

=f2-f2*g2

 

[silk]

Silk again

The formulas don't work for a variety of reasons.
One is that there is a deleted column A on my original worksheet which I just noticed last night (my computer is in storage atm and this is a lot of trouble to do in internet cafes)
Excel Jeanie copies it's own version of the worksheet.
If there is no column a Excel Jeanie just accepts coloumn B as being Column A and prints it in the forium that way.

Anyway the main point is - :

My original post is wrong

I need three formulas

1) the days difference which I did

2) a new column for the discount (in dollars and taken from the formula used with the VLOOKUP table)

3) a third formula for the new revised fees paid

So the original table and it's formulas are invalid anyway. This is how the worksheet should look
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"><COL style="WIDTH: 117px"></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><TD>H</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="TEXT-ALIGN: center; FONT-WEIGHT: bold">Discount</TD><TD style="TEXT-ALIGN: center; 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">9-Sep</TD><TD style="TEXT-ALIGN: right">1-Sep</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="TEXT-ALIGN: right">$395.00</TD><TD> </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><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><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">1-Sep</TD><TD style="TEXT-ALIGN: right">5-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$690.00</TD><TD> </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">9-Sep</TD><TD style="TEXT-ALIGN: right">31-Aug</TD><TD> </TD><TD style="TEXT-ALIGN: right">$395.00</TD><TD> </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><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><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><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><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><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><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><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><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><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><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><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><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><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><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD> </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">22</TD><TD> </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">23</TD><TD> </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">24</TD><TD> </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">25</TD><TD> </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">26</TD><TD> </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


Which is nothing like I did in the first place.

 

[dabbler68]

Hey silk,
For what you need, the following will work in the way in which you have described.

In cell E2 your formula is fine

=C2-D2

In cell G2, the formula should be

=VLOOKUP(E2,$E$14:$F$17,2,1)*F2

In cell H2, the formula should be

=F2-G2

....and as tai said, change the value in your table in E14, to -999, if you have any payments which will be made after the 'commencement date', so that the formula in H2 doesn't error

Format F2, G2 and H2 as currency and you should be fine

Please let us know how it goes, and HTH's

 

[silk]

Sorry.

I did a reply post on wednesday where I said thanks for all the help.

I'm amazed to find it's not there.

Thanks very much for your help Taigovinda and Dabbler, it is extremely appreciated.

 

[dabbler68]

Hey Silk,
That's no problem, and thanks for getting back to us (again) to make sure we knew how it went

Glad it worked out for you, and happy to help

Cheers