Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Phrasing a formula (E2000)

    Hi folks

    Back again: I cannot get my head around a formula problem, I have a transport charge that is fixed up to 500 kilos (

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Try :
    =IF(I10<=500,U10,IF(I10>500,U10+V10*(I10-500)))
    Francois

  3. #3
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Thanks for your prompt response, thats fantastic

    Stephen

  4. #4
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    =12.50+max((I10-500)*0.14,0)

    is another way

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Thanks for your input Andrew

    Stephen

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Hi Andrew

    I've missed the presence of the Step Function (or Heaviside function) in Excel on more than one occassion:
    <IMG SRC=http://397.port5.com/heav.gif>
    To this end, I use the following UDF:
    <pre>Public Function Heav(Val As Long, xVal As Long) As Integer

    If Val > xVal Then
    Heav = 1
    Else
    Heav = 0
    End If

    End Function
    </pre>

    In this context, the formula would be:

    = 12.50 + 0.14 * (I10 - 500) * Heav(I10, 500)
    = U10 + V10 * (I10 - 500) * Heav(I10, 500)

    It looks more cumbersome than your usage of max( ) and it would seem that max( ) could do everything the step function does. I might just lighten the load and get rid of it.

    Alan

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    That's actually a little more complicated than needed:

    <pre>=IF(I10<=500,U10,U10+V10*(I10-500))
    </pre>

    Legare Coleman

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    I very much like Andrew's approach in <post#=409755>post 409755</post#> because it's reflective of the discontinuous nature of the function:
    =12.50+max((I10-500)*0.14,0)

    This could be easily and "readably" extended to accommodate, say, a table of five cost rates that vary discontinuously with weight. I don't know how its efficiency compares with a nested if( ) but it's certainly a lot less cumbersome than using my UDF step function.

    Alan

  9. #9
    5 Star Lounger
    Join Date
    Oct 2002
    Location
    Wellington, Wellington, New Zealand
    Posts
    621
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Efficiency of the Max variant is likely to be much worse than IFs - I'm picking you'll lose at least a nano-second
    However, even thirty years ago efficiency should have taken second place to readability and resiliency for most applications.

    I agree, for multiple steps, Max tends to present a more readable result than nested IFs.
    Another alternative that reflects the step better is

    =12.50 + IF(I10>500,(i10-500)*0.14)

    This also extends in a more 'natural' way for multi-steps e.g.

    =12.50 + IF(I10>500,(i10-500)*0.14) + IF(I10>1000,(i10-1000)*0.14) ...

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Phrasing a formula (E2000)

    Thanks Andrew. Looks just as readable, and probably infinitely more efficient <img src=/S/grin.gif border=0 alt=grin width=15 height=15>, as you say. Ironically, the main purpose of my UDF was aimed at readability, but it didn't work out that way. Its use would have been appropriate in a mathematical representation, where things like IF( ) don't have a place, but wasn't such a bright idea for XL - old habits die hard.

    Alan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •