Results 1 to 10 of 10
Thread: Phrasing a formula (E2000)

20040921, 06:23 #1
 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 (

20040921, 06:42 #2
 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*(I10500)))Francois

20040921, 08:09 #3
 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

20040921, 08:19 #4
 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((I10500)*0.14,0)
is another way

20040921, 11:48 #5
 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

20040921, 12:25 #6
 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

20040921, 18:25 #7
 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*(I10500))
</pre>
Legare Coleman

20040922, 00:06 #8
 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((I10500)*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

20040922, 18:01 #9
 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 nanosecond
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,(i10500)*0.14)
This also extends in a more 'natural' way for multisteps e.g.
=12.50 + IF(I10>500,(i10500)*0.14) + IF(I10>1000,(i101000)*0.14) ...

20040922, 23:20 #10
 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