ExcelBanter - multiple inputs, multiple outputs

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))))

"rjmckay" wrote:

This is an example of what I'm trying to do....

If you enter $X you will recieve $Y commission.

The trick however, is that there are 12 different amounts that are
appropriate to enter.... and each of those 12 require an equation to
figure out what the commision will be.

for example 12 might have the formula of (12/6)-(1/3) in order to
figure out the commission

11 might be (11/6)-(1+(2/3))

so on and so forth...

I tried doing if, if, if, if, if ... so on... but eventually reached
the limit of how long the string can be. LOL

Is there a way I can accomplish this?

In the meantime, I simply caluclated everything out by hand and put it
in a note, telling the user what data to imput for what he needs...
but I would much rather have the user just insert the $ amount and the
computer does it's work and figures out the commission.

Is there a way?:confused:

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