Help with a problem have tried a number of things

[dexman]

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!

[Biff]

Hi!

Can you post a sample file?

Biff

"dexman" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[dexman]

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

[Biff]

Hi!

Try this:

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

Biff

"dexman" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[dexman]

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.