After 2 days of frustration...Formula

[TonyMorcom]

Please help me someone.
I have a work book with 3 worksheets. 2 & 3 are complete and function
perfectly, more by trial and error than expertise.
Work sheet one is causing real problems:-

Assume the following info:

Column A Amount in £ individually from £500~£4999, duplicated by term.
Column B Term 180months,120,108,96,84,72,60,48,36 & 24.
Scenario is that for each amount in £ I need to have a term its over so
that i have entered the contents of the columns as follows
A1 500
A2 501
A3 502
etc down to
A4999
Against each row in Column B is the term so for 1st 4999 rows B shows
180, then the next 4999 B shows 120 etc)

Column C = relevant APR
Column D = relevant 1/4 term,
Column E = 1/2 term
Column F = 3/4 term.

There are a further 4 columns which are based on deferred payents
producing the corresponding values so that a formula will give a
true/false answer in figures as I have done on the other work sheets.
They were no real problem as each only the term was variable not the
amount.

I have the problem that I need to produce a formula which will generate
a result based on the entry of Amount and Term combining to give a
result from the following columns. Once I get one working I can get the
others working. Any help would be tremendously useful. Tony

 

[RichardSchollar]

Hi Tony

What do you actually want to calculate (in words)? When you say such
and such a column is 1/4 term, do you mean it is the balance outstandng
to be repaid one quarter thru the term, or is it simply the number of
months represented by the quarter term (ie for a 120 month term, this
would be 30)?

Are you sure you need to have all this listed down 5000-odd rows for
each term? It does seem like a rather horrendous duplication of effort?
Do you have need to have all of them held on the spreadsheet?

Best regards

Richard

 

[TonyMorcom]

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