Conditional Formatting

[dandandan90]

I want to make a table that will calculate interest on overdue bills. the
problem i've hit is that we have a variable interest rate that is charged.
its dependant on the bank of england base rate. so between certain dates it
will be charged at one value and between other dates it will provide another.
im doing this back to 2000 so there are alot of changes in the interest rate.
for example between 01/07/09 and 01/08/09 the base rate stood at 4% so 4%
interest can be charged.
we can charge interest from 05/07/09 (when the bill was sent)
so i do a conditional formula that if 05/07/09 is less than 01/08/09 but
greater than 01/07/09 then the value 4% will be charged
i need the cell that i put this formula in to test against 30 different date
ranges each with a different rate of interest applicable
so when i put a date in a cell the conditional format checks to see if it
lies within these date ranges and then the cell appears as the applicable
rate of interest.

sorry if this isn't clear it may not even be conditional formatting that i
need to look at, any help much appreciated though

 

[Max]

I would view this to be the key issue (not the CF):

.. test against 30 different date ranges
each with a different rate of interest applicable

Assume the reference table is within A1:B31, where
A2:A31 houses the start-dates
B2:B31 houses the end-dates
& C2:C31 houses the corresponding interest rates

All dates are presumed real dates recognized by Excel, with full,
non-overlapping coverage for the entire date range that's going to be
referenced

Assume you have the dates (these must be real dates) listed in E2 down
You could use this in F2, normal ENTER:
=INDEX($C$2:$C$31,MATCH(1,INDEX((E2>=$A$2:$A$31)*(E2<=$B$2:$B$31),),0))
to extract the applicable interest rate from the reference table

voila? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---