Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple inputs, multiple outputs
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? -- rjmckay ------------------------------------------------------------------------ rjmckay's Profile: http://www.excelforum.com/member.php...o&userid=16880 View this thread: http://www.excelforum.com/showthread...hreadid=550191 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? -- rjmckay ------------------------------------------------------------------------ rjmckay's Profile: http://www.excelforum.com/member.php...o&userid=16880 View this thread: http://www.excelforum.com/showthread...hreadid=550191 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple inputs, multiple outputs
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! -- rjmckay ------------------------------------------------------------------------ rjmckay's Profile: http://www.excelforum.com/member.php...o&userid=16880 View this thread: http://www.excelforum.com/showthread...hreadid=550191 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generate 1-to-1 outputs for a list of inputs into a excel formula | Excel Discussion (Misc queries) | |||
Is there a function like "Data Table" for multiple Inputs | Excel Worksheet Functions | |||
Multiple inputs one output | Excel Discussion (Misc queries) | |||
Using solver with function with multiple outputs | Excel Worksheet Functions | |||
One cell takes multiple inputs | Excel Discussion (Misc queries) |