Hi Richard

I work for a double glazing company and am trying to create a
simplified finance ready reckoner to help give schedules direct to reps
over the phone. It was easy up until now because we only had one finance
rate ~ There are now 3 depending upon the value of order. £5000 and over
is simple because the APR and the amount owing per 1000 over 1/4, 1/2
and 3/4 term are only varied by whether or not the customer uses
deeferred or non deferred payments. There for it was simply a case of
having a table with the rates in one column and the other info in
adjacent columns and using simple true or false formulas.

However below £5000 everything changes. Every £500 the associated
information changes. This is then complicated by the fact that there
are also options of 180,120,108,96,84,72,60,48,36 & 24 months over
which they can pay. Theorectically then there are 4500x10 rows of info.
(Finance is only applicable over £500. So the columns I have are laid
out as follows:

N: Value
O: Term
P: APR Std
Q: 1/4 Std
R: 1/2 Std
S: 3/4 Std
T: APR Def
U: 1/4 Def
V: 1/2 def
W: 4/4 Def


On my control Panel that actaully generates answers from several other
tables and works perfectly except this bit, I enter the Value in B11,
The Deferred (1 or 4 months) in B7 and the term in B5. The result using
a true/false scenario revolving around B7 should produce 4 answers in
set up cells. I set of answers will be for 1 month deferred(true), the
other set for 4 mnths def(false) The answers required for each value,
remembering that the value could be literally anywhere between £500 &
£4999 albeit it round £ will be the relevant APR, 1/4, 1/2, and 3/4
amounts. I have the info to put in the database but I keep coming up
with errors which seem to be generated by the fact that there are 10
sets of duplicated £values. I have even tried changing format to text,
no different, merging the value and term columns to give 500180,501180
etc and still no joy. I can get it to work for one term using one
formula about 500 formula's ago(!!) but then it doesnt work with other
terms. Hope this is clearer Richard. It would make things simpler if I
could find a way to have a column in which was entered 1 unique entry
for each combination of value and term, and then create a formula which
would be auto generated when B5 and B11 are entered????

Be pleased to hear any help you can give me.

Tony


--
TonyMorcom
------------------------------------------------------------------------
TonyMorcom's Profile: http://www.excelforum.com/member.php...o&userid=35056
View this thread: http://www.excelforum.com/showthread...hreadid=547987