Nested If Exceeding 7 Calculations

  • Thread starter Thread starter wilma2299
  • Start date Start date
W

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!
 
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!!
 
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
 
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 -
Click to expand...

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?
 
Not really
Click to expand...

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, ;-)
 
Not really

--
Don Guillett
SalesAid Software






- Show quoted text -
Click to expand...

I guess we could start with just a macro that can execute a series of
13 if statement conditions...
 
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.
 
Back
Top