multiple inputs, multiple outputs

[rjmckay]

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 ar
appropriate to enter.... and each of those 12 require an equation t
figure out what the commision will be.

for example 12 might have the formula of (12/6)-(1/3) in order t
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 reache
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 i
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 th
computer does it's work and figures out the commission.

Is there a way?:confused

 

[Guest]

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]

I GET IT!!!!

Thank you so much guys... after studying the help file for Vlookup... I
figured out everything you all where trying to tell me....

Thank you!