multiple inputs, multipul outputs (part 2)

[rjmckay]

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

As long as your formula can always be expressed as you've shown it w
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
because
perhaps it isn't always 6?

Lets say your amounts are in column A and you want the commission t
show up
in column B next to it. In A1 you put 11 and in A2 you put 12, an
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 an
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,
)/VLOOKUP(A2,$F$1:$J$12,5)))

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

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

One more thing - if you don't have an "amount" in column A, then you'l
get
a #NA error in column B where you've placed the formula - thi
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,
)/VLOOKUP(A2,$F$1:$J$12,5))))

Ok....

There are 12 variables that a person could enter in the box... howeve
rather than waste your time I'm just going to give 2... I should b
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 ad
up to 115 over the course of a year... in fact, it's more. and sam
with the 19.35

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

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

and here's my code (and it does work) ... A1 in this example is th
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 differen
numbers.... but of course I only told you 115 and 225 in thi
example....

but.... if the enter 115 ..... I need Excel to figure out the firs
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'l
understand what I'm trying to ask

 

[Guest]

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
(e-mail address removed)
remove nospam for email addy!

 

[rjmckay]

i must be a bit dim.I would use some"helper" cells to do some figurin
so
that the 115/12 and 225/12 actually just refferred to another cel
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 i
your
formula.I find that the easist way to figure these things out is t
proceed
one step at a time ,making each calcuation"automatic".Once you hav
done that
you can combine all your seperate steps together into one forula

--

paul
(e-mail address removed)
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 t
multiply by 2 ...* BUT *keep the 3/5 in tact .... now you need to d
(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 tha
on a Euricka moment. =

 

[rjmckay]

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!