Last time I posted, I used such a weak example, that when JLatham
responded with his answer below... all it showed is that it's
possible, but because my example was so week the answer didn't help
much... after the quote I want to pose a real life example of the
question I'm trying to ask with real figures...

As long as your formula can always be expressed as you've shown it we
can use
VLOOKUP() and a table for the 12 amounts to calculate it.
You sholwed 2 formulas:
(12/6)-(1/3)
and
(11/6)-(1+(2/3))
but the first one could also be expressed as
(12/6)-(0+(1/3))
So a general expression would be:
(X/a)-(b+(c/d))
where 'b' can be zero or some other number. I used 'a' instead of 6
because
perhaps it isn't always 6?

Lets say your amounts are in column A and you want the commission to
show up
in column B next to it. In A1 you put 11 and in A2 you put 12, and
continue
down column A entering one of those 12 possible amounts.

Now set up a table somewhere - for this example I'll start it at F1 and
it
will take up 12 rows and 5 columns:
F G H I J
11 6 1 2 3
12 6 0 1 3
25 6 2 1 3
30 6 0 1 3
35 6 1 2 3
40 6 2 3 5
45 6 0 3 5
50 6 1 2 5
55 6 2 2 5
60 6 0 1 3
65 6 1 2 3
70 6 2 1 4
amt 'a' 'b' 'c' 'd'

In B1 you would enter this formula:
=(A2/VLOOKUP(A2,$F$1:$J$12,2))-(VLOOKUP(A2,$F$1:$J$12,3)+(VLOOKUP(A2,$F$1:$J$12,4
)/VLOOKUP(A2,$F$1:$J$12,5)))

that should all be on one line - the format here may break it into 2 or
more
lines.
Then just drag that formula down the page. Substitute the actual
location
of your table for $F$1:$J$12 in the formula.

By the way - the reason your previous effort failed probably wasn't due
to
the line being too long, but because you had to try to use more than 7
nested
IF() statements - Excel has a limit of 7 nested functions in a
formula.

One more thing - if you don't have an "amount" in column A, then you'll
get
a #NA error in column B where you've placed the formula - this
variation of
the formula will fix that, again, just one long line:
=IF(ISNA((A2/VLOOKUP(A2,$F$1:$J$12,2))),"",(A2/VLOOKUP(A2,$F$1:$J$12,2))-(VLOOKUP(A2,$F$1:$J$12,3)+(VLOOKUP(A2,$F$1:$J$12,4
)/VLOOKUP(A2,$F$1:$J$12,5))))


Ok....

There are 12 variables that a person could enter in the box... however
rather than waste your time I'm just going to give 2... I should be
able to extrapolate how to do the rest...

115, and 225

now it's 115 for the year, and 225 for the year...

however, 115 matches up to 9.89/month and 225 matches up to 19.35/mo

So, if you figured it out you'd quickly find out that 9.89 does not add
up to 115 over the course of a year... in fact, it's more. and same
with the 19.35

So I first need to force 115 and 225 to match their respective monthly
charges...

Then I need to say for the first 6 months you make 200% ... the second
6 months you make 10%

and here's my code (and it does work) ... A1 in this example is the
quantity
purchased

example for 115:

IF(A1=0,0,(A1*(((((115/12)+((23*A1)/(75*A1)))*6)*2)+(((115/12)+((23*A1)/(75*A1)))*6)*0.1)))

example for 225:

IF(A1=0,0,(A1*(((((225/12)+((3*A1)/(5*A1)))*6)*2)+((((225/12)+((3*A1)/(5*A1)))*6)*0.1))))

Now... the problem I face is that they can insert up to 12 different
numbers.... but of course I only told you 115 and 225 in this
example....

but.... if the enter 115 ..... I need Excel to figure out the first
code

but if they enter 225 ... I need Excel to figure out the second code.

I tried to make this question as clear as possible... hopefully you'll
understand what I'm trying to ask.


--
rjmckay
------------------------------------------------------------------------
rjmckay's Profile: http://www.excelforum.com/member.php...o&userid=16880
View this thread: http://www.excelforum.com/showthread...hreadid=550681