# Thread: Phrasing a formula (E2000)

1. ## 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. ## Re: Phrasing a formula (E2000)

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

3. ## Re: Phrasing a formula (E2000)

Thanks for your prompt response, thats fantastic

Stephen

4. ## Re: Phrasing a formula (E2000)

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

is another way

Stephen

6. ## 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. ## Re: Phrasing a formula (E2000)

That's actually a little more complicated than needed:

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

8. ## 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. ## 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. ## 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
•