Nested If Exceeding 7 Calculations

[wilma2299]

I'm trying to have a calculation executed, but it has more than 8
arguements. What I need is the following, given a certain quota (I'll
have 6 different quotas to be looked up), find that range and then
execute the calculation. I have 13 different ranges and 13 different
calculations corresponding. I read that I can use a lookup, but I
would rather write a macro, but I have no idea where to start. Thanks!

 

[Guest]

It would helpful if we knew what calculations are to be done on what data!

 

[Don Guillett]

Look in vba help index for select case

 

[wilma2299]

Thank you for responding so quickly! The calculations are as follows:

Let's say the profit was $57848 and the quota was $62500 making the
quota met 92%. This falls into the quota range of 90 - 94, and
therefore it would be calculated:
(57848*.95)+(57848*.475) where .95 and .475 change with every quota
range and I have 13 quota ranges. I really appreciate the
assistance!!

 

[wilma2299]

I should look where?

Look in vba help index for select case

--
Don Guillett
SalesAid Software





- Show quoted text -

 

[Don Guillett]

You still didn't give too much info but this is the idea.

Sub selectcasevalues()
Select Case ActiveCell.Value
Case Is > 94: x = 0.95 + 0.475
Case Is > 100: x = 1.2 + 5
'etc

Case Else
End Select
ActiveCell.Offset(, 1) = Range("o11") * x
End Sub

 

[wilma2299]

You still didn't give too much info but this is the idea.

Sub selectcasevalues()
Select Case ActiveCell.Value
Case Is > 94: x = 0.95 + 0.475
Case Is > 100: x = 1.2 + 5
'etc

Case Else
End Select
ActiveCell.Offset(, 1) = Range("o11") * x
End Sub

--
Don Guillett
SalesAid Software






- Show quoted text -

Let me see if i can make it a little more clear. I have six different
regions. There are different tiers of quota's that when met,
calculate different bonus payouts for each region. Does that clear
up anything?

 

[Don Guillett]

Not really

 

[ceebu]

Not really

Could;nt you use dcount ? Excel's in built help has some example.. how
i have done this in the past is to actually create a range of
criteria's specific to each if statement and use that in the formula..
it has some limitations though.. i will let you explore the help, ;-)

 

[wilma2299]

Not really

--
Don Guillett
SalesAid Software






- Show quoted text -

I guess we could start with just a macro that can execute a series of
13 if statement conditions...

 

[Don Guillett]

You could but a select case macro is better. Suit yourself.
It seems that you do not need assistance enough to fully explain your need.