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

i must be a bit dim.I would use some"helper" cells to do some figuring so
that the 115/12 and 225/12 actually just refferred to another cell which is
"box"/12.I cant figure out tho where (23*A1)/(75*A1) and (3*A1)/(5*A1) come
from in relation to 115 and 225 respectively.If there is no direct
relationship then they can be looked up in a table and referred to in your
formula.I find that the easist way to figure these things out is to proceed
one step at a time ,making each calcuation"automatic".Once you have done that
you can combine all your seperate steps together into one forula

--

paul

remove nospam for email addy!



"rjmckay" wrote:


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

paul Wrote:
i must be a bit dim.I would use some"helper" cells to do some figuring
so
that the 115/12 and 225/12 actually just refferred to another cell
which is
"box"/12.I cant figure out tho where (23*A1)/(75*A1) and (3*A1)/(5*A1)
come
from in relation to 115 and 225 respectively.If there is no direct
relationship then they can be looked up in a table and referred to in
your
formula.I find that the easist way to figure these things out is to
proceed
one step at a time ,making each calcuation"automatic".Once you have
done that
you can combine all your seperate steps together into one forula

--

paul

remove nospam for email addy!



HeHe... you're not dim ... I only chanced upon that myself...

You see, if you do 2 * (3/5) you get 1.2 .... however, if you want to
multiply by 2 ...* BUT *keep the 3/5 in tact .... now you need to do
(3*2)/(5*2) which naturally brings you back to 3/5.

That's why my math is done that way. You're not dim, I came upon that
on a Euricka moment. =)


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

I GET IT!!!!

After studying the Vlookup on the help file... I now understand
everything (well mostly everything) you guys where trying to tell me!
Yes, Vlookup is DEFFINATELY the way to go here!

Thank you all very much!


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