I am trying to calc a value based on several criteria please see below,
I have written the problem in plain english. 7 nested IF statements
stops me cold, I know there has to be a way to do this, please help me
if you can.

Here are the possibilities in list format

This is the first set of possible choices, they are in a drop down and
return the values of 1,2,3,4,5,6,7 depending on the users choice.

TermInMonths = (12,24,36,39,48,60,63)

The second set of possible choices, which are in a seperate drop down
return the values of 1,2 depending on the users choice

LeaseType = (Fair Market Value, 1.00 Buy Out)

So as you can see you can have a lease of 1 of 2 types and it can then
be a term of 12,24,36,39,48,60 or 63 months in length

There is a table that holds the rates based on the amount being leased.
There is a seperate table of rates for leases that are under $3000.00
for both lease types and a seperate table of rates for leases $3000.00
and up for both lease types.

In summary I need to determine lease type, term length and cost
threshold, get the apropriate rate based on that criteria and then
multiply that rate by the cost of equipment.

I hope this helps everyone understand what I am trying to accomplish,
someone rescue my brain please!!

psuedo code:

IF Lease Type = 1 and lease term = 36 and cost is below 2999.99 then
use this factor to calculate cost. ELSEIF...

As you can see from the list of possibilities this IF statement would
be hellish, there has got to be a way!

Thanks in advance!


--
dexman
------------------------------------------------------------------------
dexman's Profile: http://www.excelforum.com/member.php...o&userid=31481
View this thread: http://www.excelforum.com/showthread...hreadid=515153

Hi!

Can you post a sample file?

Biff

"dexman" wrote in
message ...

I am trying to calc a value based on several criteria please see below,
I have written the problem in plain english. 7 nested IF statements
stops me cold, I know there has to be a way to do this, please help me
if you can.

Here are the possibilities in list format

This is the first set of possible choices, they are in a drop down and
return the values of 1,2,3,4,5,6,7 depending on the users choice.

TermInMonths = (12,24,36,39,48,60,63)

The second set of possible choices, which are in a seperate drop down
return the values of 1,2 depending on the users choice

LeaseType = (Fair Market Value, 1.00 Buy Out)

So as you can see you can have a lease of 1 of 2 types and it can then
be a term of 12,24,36,39,48,60 or 63 months in length

There is a table that holds the rates based on the amount being leased.
There is a seperate table of rates for leases that are under $3000.00
for both lease types and a seperate table of rates for leases $3000.00
and up for both lease types.

In summary I need to determine lease type, term length and cost
threshold, get the apropriate rate based on that criteria and then
multiply that rate by the cost of equipment.

I hope this helps everyone understand what I am trying to accomplish,
someone rescue my brain please!!

psuedo code:

IF Lease Type = 1 and lease term = 36 and cost is below 2999.99 then
use this factor to calculate cost. ELSEIF...

As you can see from the list of possibilities this IF statement would
be hellish, there has got to be a way!

Thanks in advance!


--
dexman
------------------------------------------------------------------------
dexman's Profile:
http://www.excelforum.com/member.php...o&userid=31481
View this thread: http://www.excelforum.com/showthread...hreadid=515153

Here is where you can get the actual file

http://www.stuffilike.us/files/configurator.xlt

Anyone that would like to help...

the tabs that I have started working with are the VOS WaveScan tab. The
factors in which to calc the payments are on the Factors Tab.

Thanks


--
dexman
------------------------------------------------------------------------
dexman's Profile: http://www.excelforum.com/member.php...o&userid=31481
View this thread: http://www.excelforum.com/showthread...hreadid=515153

Hi!

Try this:

=IF(G4<3000,G4*INDEX(Factors!C2:D8,DA2,CY2),G4*IND EX(Factors!C10:D16,DA2,CY2))

Biff

"dexman" wrote in
message ...

Here is where you can get the actual file

http://www.stuffilike.us/files/configurator.xlt

Anyone that would like to help...

the tabs that I have started working with are the VOS WaveScan tab. The
factors in which to calc the payments are on the Factors Tab.

Thanks


--
dexman
------------------------------------------------------------------------
dexman's Profile:
http://www.excelforum.com/member.php...o&userid=31481
View this thread: http://www.excelforum.com/showthread...hreadid=515153

That works like a champ. I am still trying to understand the INDEX(),
but it does the trick.

Thanks again for solving this issue for me.:)


--
dexman
------------------------------------------------------------------------
dexman's Profile: http://www.excelforum.com/member.php...o&userid=31481
View this thread: http://www.excelforum.com/showthread...hreadid=515153