Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
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 --- "dandandan90" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting--different formatting depending on cell con | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |