Q for Dana DeLouis

M

M Thompson

Hi Dana, and thanks again for the following:

=MEDIAN(-400,-MIN(3 + 0.02* H9, 0.05* H9),0)

Can you elaborate on it? Math is a shortcoming for me, so I'm definitely
not getting the min calc. And why median?

Thanks and have...
 
B

Bernard Liengme

Firstly, the formula evaluates MIN(3+.0.02*H9, 0.05*H9)
So if H9 holds the value -1, it finds MIN(3-0.02, -0.05) which is -0.05
Then if finds the MEDIAN of -400, -0.05 and 0
best wishes
 
D

David Biddulph

As this presumably relates to a previous thread, you ought to ask the
question as a follow-up to the relevant message on the previous thread.
MEDIAN and MIN are both standard Excel functions, so you'll find descritions
and examples if you type the function name into Excel help.
MEDIAN is an easy way of limiting a formula's output to a value between 2
limits.
=MEDIAN(min_val,formula,max_val) will give an output of formula if formula
lies between min_val and max_val, but will give min_val if formula evaluated
to below min_val, and will give an output of max_val if formula gives a
value greater than max_val.
MIN gives you an output of the lowest of the range of input values, so if
0.05*H9 is less than 3+0.02*H9 it will return 0.05*H9, otherwise it will
return 3+0.02*H9.

If you struggle with maths, then you're likely to struggle with any
spreadsheet, but if you can give examples of values of H9, what formula you
are using, what value you are getting out of your formula, what you
expected, and why.
 
M

M Thompson

Hello David
You're right, this does relate to a previous thread where I asked for
clarification, but my question wasn't replied to. I posted it this way, in
case Dana just missed my first request. Also, I did check out the help menu
and several large Excel books, so I had some, but not all, of the idea.

The problem is: 5% of the 1st 100, 2% thereafter. The other two boundaries,
of course, were -400 and 0. The formula was absolutely spot-on, but I would
like to understand how the 3+, etc., are getting the answer.

Thanks for the help
 
D

Dana DeLouis

Hi. How are you?
Your commission schedule is really two different linear lines.
If you plot the graph in the valid range, you would have two lines,
similar to line graphs you are familiar with ie y = a*x + b

0.05*x + 0, and 0.02*x + 3

It's a decreasing commission rate (needing MIN()), but a graph would
also show that the valid solution is the minimum of the two.

Instead of Min(Max(0,x),400)

I just like to use Median(0,x,400)

I find it easier to read and understand. Plus, I always seem to mess up
the Min(Max( equation.

I have a few programs that do a lot of this work for me, but here is
something you can do about your question on +3.
The first equation is pretty obvious... ie 0.05*x + 0

To get an equation of the second line, we need two points.
One point could be {100,5}. The second line grows by 2%. Let's pick an
increase of 100, hence the output grows by 2. A simple second point
could therefore be {200,5+2=7}

In Cells[A1:B2] put
100 , 5
200 , 7

It's easy here to see that the zero intercept in +3.
For more complex stuff, select this data, and do a x-y scatter chart on
the data. Add a trend-line (swap x-y), and display the equation of that
trend-line. You will get the same...0.02*x + 3

Hope some ideas here are helpful.
Dana DeLouis

= = = =
 
D

Dana DeLouis

Hi. Just to add. Charts are nice to start off with, but you may also
want to look at:

In Cells[A1:B2] put
100 , 5
200 , 7

=SLOPE(B1:B2,A1:A2)
=INTERCEPT(B1:B2,A1:A2)

HTH
Dana DeLouis


<snip>
 
M

M Thompson

Hi Dana
Thanks for the detailed explanation. I've gotten some of it and will
continue working at it. By the way, it appears like you tried to paste
something in as a visual, but all that came across was the placeholder <snip>


--
OneFineDay


Dana DeLouis said:
Hi. Just to add. Charts are nice to start off with, but you may also
want to look at:

In Cells[A1:B2] put
100 , 5
200 , 7

=SLOPE(B1:B2,A1:A2)
=INTERCEPT(B1:B2,A1:A2)

HTH
Dana DeLouis


<snip>
Click to expand...
 
Top