Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
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... -- OneFineDay |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "M Thompson" wrote in message ... 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... -- OneFineDay |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
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. -- David Biddulph "M Thompson" wrote in message ... 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... -- OneFineDay |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
Thanks for the input, Bernard. It helped clarify several things for me.
-- OneFineDay "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "M Thompson" wrote in message ... 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... -- OneFineDay |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
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 -- OneFineDay "David Biddulph" wrote: 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. -- David Biddulph "M Thompson" wrote in message ... 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... -- OneFineDay |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for 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 = = = = M Thompson wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for 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 but I would like to understand how the 3+, etc., are getting the answer. <snip |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Q for Dana DeLouis
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" wrote: 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 but I would like to understand how the 3+, etc., are getting the answer. <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dana: help with your Demo() macro to "expand" formulas | Excel Worksheet Functions |