Excel: Can't understand complex formulas

[Tezdread]

Hi,

I was asked to create a database and up until we got to the conversion of the excel workbook that they have been using up until now.

There are about 10 cells that either have a number or y n
I gone though all the other formulas and I should be alright coverting these into queries.

I don't understand what is happening with these formulas though so any help would be appreciated.

=IF(OR(T8="No",R8<'Limits allocated'!$B$11),"R9=decline",IF(R8<'Limits allocated'!$C$11,'Limits allocated'!$B$12,IF(R8<'Limits allocated'!$D$11,'Limits allocated'!$C$12,IF(R8<'Limits allocated'!$E$11,'Limits allocated'!$D$12,IF(R8<'Limits allocated'!$F$11,'Limits allocated'!$E$12,IF(R8>='Limits allocated'!$G$11,'Limits allocated'!$F$12,"no"))))))

and


=INDEX('Limits allocated'!$B$12:$H$20,MATCH(S8,'Limits allocated'!$A$12:$A$20,0),MATCH(U8,'Limits allocated'!$B$12:$H$12,0))

Not sure if you need the other formulas for it to make sense?

Tezdread
&quot;With every solution comes a new problem&quot;

 

[Zathras]

Here is an exploded view of the first formula:
[tt]
=IF(OR(T8="No",R8<'Limits allocated'!$B$11),
"R9=decline",
IF(R8<'Limits allocated'!$C$11,
'Limits allocated'!$B$12,
IF(R8<'Limits allocated'!$D$11,
'Limits allocated'!$C$12,
IF(R8<'Limits allocated'!$E$11,
'Limits allocated'!$D$12,
IF(R8<'Limits allocated'!$F$11,
'Limits allocated'!$E$12,
IF(R8>='Limits allocated'!$G$11,
'Limits allocated'!$F$12,
"no"
)
)
)
)
)
)
[/tt]

Limits allocated sheet looks something like this:[tt]
A B C D E F G
11 From 10 20 30 40 50 60
12 Thru 19 29 39 49 59 n/a
[/tt]
There are two special cases:
1. Cell T8 = "No"
2. Cell R8 < Lowest number in Limits table (10 in my example)

If it is one of the special cases then result = "R9=decline"

If it is not one of the special cases then the value in R8
is tested against the table to see where it falls, selecting
the maximum value for the bracket in which it is found.

e.g. if R8 = 35 then it is less than 40 ($E$11) and so the
result is 39 (from $D$12)

There is actually a bug in the formula because when R8=55
it is not less than 50 ($F$11) and it is not >= 60 ($G$11)
so the formula returns "no" whereas when R8=65 it is >= 60
and correctly returns 59 ($F$12)

For the second one, look up the INDEX and MATCH functions in the help file. It's not that hard.

 

[Zathras]

BTW, the first formula should be re-written using HLOOKUP to simplify the formula (and remove the bug). Again, you can find it in the help file.

Alternately you can use the VLOOKUP function if you re-arrange the data table.