Multiple Formulae

Deon,

This formula is one way to do it:

=IF(B3>5,(4*8.25)+((B3-5)*6.95)+12.25,IF(B3>1,12.25+((B3-1)*8.25),12.25))

Try this formula too, displays nothing if B3 = 0 or is empty

Try this one as well:
=IF(B3=1,12.25,IF(AND(B3>1,B3<6),12.25+(8.25*(B3-1)),IF(B3>5,45.25+(6.95*(B3-5)),"")))

One question, what are you spraying those horses with?

Formula "horse sense" tip

Deon,

Leroy's and my formula will do the trick for you, but for a more "stable" solution, your best bet might be to consider placing your spray costs per horse increment in separate cells, and reference those cells in the formula, instead of as hard numbers. My formula suggestion provides hard numbers but it's not a good practice generally.

Good luck.

Tom Urtis

I agree with Tom......

Apart from my formula not completely working (should have mutliplied last 12.25 by B3), I agree it would be smart practice to soft code the formula - make it easier when you put your prices up ;-)

Use one of:

=IF(B3,12.25+IF(B3<=5,(B3-1)*8.25,4*8.25+(B3-5)*6.95),"")

=(B3>=1)*(12.25+(((B3<=5)*((B3-1)*8.25)+(B3>5)*(4*8.25+(B3-5)*6.95))))

The second is the Boolean version which is faster.

Aladin

========== I have a problem from one of our stock inspectors, he needs a formula that will work out the cost of spraying horses. ie cell B3 holds the number of horses

>The second is the Boolean version which is faster.

How much faster? Is it measurable?

Use one of: =IF(B3,12.25+IF(B3<=5,(B3-1)*8.25,4*8.25+(B3-5)*6.95),"") =(B3>=1)*(12.25+(((B3<=5)*((B3-1)*8.25)+(B3>5)*(4*8.25+(B3-5)*6.95)))) The second is the Boolean version which is faster. Aladin ========== : I have a problem from one of our stock inspectors, he needs a formula that will work out the cost of spraying horses. ie cell B3 holds the number of horses : 1 horse = $12.25

How much faster? Is it measurable?

Operators like +, >, etc work faster than functions.
We need a timer to assess it of course. Folks at http://www.decisionmodels.com/ have code that can time the formulae.
The gain would be perceptiple if a column of say 1000 rows computing by means of a Boolean formula were compared with its IF-equivalent.

Aladin

======== : Use one of

Hmm ........

I'm not trying to be picky, but I used the following 2 macros to enter the formulas in 1000 cells and the recorded time for each macro was 00:00:00 - that is, less than 1 second for each of them.

Sub Macro1()
Dim t As Date
t = Now()
Range("C1:C1000").FormulaR1C1 = _
"=IF(R[2]C[-1],12.25+IF(R[2]C[-1]<=5,(R[2]C[-1]-1)*8.25,4*8.25+(R[2]C[-1]-5)*6.95),"""")"
MsgBox Format(Now() - t, "hh:mm:ss")
End Sub

Sub Macro2()
Dim t As Date
t = Now()
Range("D1:D1000").FormulaR1C1 = _
"=(R[2]C[-2]>=1)*(12.25+(((R[2]C[-2]<=5)*((R[2]C[-2]-1)*8.25)+(R[2]C[-2]>5)*(4*8.25+(R[2]C[-2]-5)*6.95))))"
MsgBox Format(Now() - t, "hh:mm:ss")
End Sub

PS ...

Also tried it on 65,000 cells and there was no perceptible difference or recorded difference.

Re: PS ...

Alan,

> I'm not trying to be picky,

Neither do I. Thanks for going thru all this.

> but I used the following 2 macros to enter the formulas in 1000 cells and the recorded time for each macro was 00:00:00 - that is, less than 1 second for each of them.

I don't know whether it makes any difference, but it could be the case that the result of the first evaluation of the formula is cached, so that no computation is necessary for the exact same instances of the formula.

What do you think?

Did you have a look at Timers.zip available at the URL that I mentioned?

Re: PS ...

I tried it on 65,000 cells with different values in column B, and timed it using the timimg function from the web site you suggested.

There appears to be no difference in the run times of each macro. The timing function returns about 0.74 seconds for each(+/- slightly on each run). Here's what I used (and subtracted A1 from A2, and A5 from A6) :-

Sub Macro1()
[A1] = "='timers.xla'!microtimer()"
Range("C1:C65000").FormulaR1C1 = _
"=IF(R[2]C[-1],12.25+IF(R[2]C[-1]<=5,(R[2]C[-1]-1)*8.25,4*8.25+(R[2]C[-1]-5)*6.95),"""")"
[A2] = "='timers.xla'!microtimer()"
End Sub

Sub Macro2()
[A5] = "='timers.xla'!microtimer()"
Range("D1:D65000").FormulaR1C1 = _
"=(R[2]C[-2]>=1)*(12.25+(((R[2]C[-2]<=5)*((R[2]C[-2]-1)*8.25)+(R[2]C[-2]>5)*(4*8.25+(R[2]C[-2]-5)*6.95))))"
[A6] = "='timers.xla'!microtimer()"
End Sub

> I'm not trying to be picky, Neither do I. Thanks for going thru all this. > but I used the following 2 macros to enter the formulas in 1000 cells and the recorded time for each macro was 00:00:00 - that is, less than 1 second for each of them. I don't know whether it makes any difference, but it could be the case that the result of the first evaluation of the formula is cached, so that no computation is necessary for the exact same instances of the formula. What do you think? Did you have a look at Timers.zip available at the URL that I mentioned?

I tried it on 65,000 cells with different values in column B and timed it using the timimg function from the web site you suggested.

There appears to be no difference in the run times of each macro. They timing function returns about 0.74 seconds for each(+/- slightly on each run). Here's what I used (and subtracted A1 from A2, and A5 from A6) :-

Sub Macro1()
[A1] = "='timers.xla'!microtimer()"
Range("C1:C65000").FormulaR1C1 = _
"=IF(R[2]C[-1],12.25+IF(R[2]C[-1]<=5,(R[2]C[-1]-1)*8.25,4*8.25+(R[2]C[-1]-5)*6.95),"""")"
[A2] = "='timers.xla'!microtimer()"
End Sub

Sub Macro2()
[A5] = "='timers.xla'!microtimer()"
Range("D1:D65000").FormulaR1C1 = _
"=(R[2]C[-2]>=1)*(12.25+(((R[2]C[-2]<=5)*((R[2]C[-2]-1)*8.25)+(R[2]C[-2]>5)*(4*8.25+(R[2]C[-2]-5)*6.95))))"
[A6] = "='timers.xla'!microtimer()"
End Sub

Re: PS ...

Thanks, Alan. I must say I'm surprised. The programmers of the Excel
application seem to have implemented IF pretty effectively.

Aladin

===== I tried it on 65,000 cells with different values in column B, and timed it using the timimg function from the web site you suggested. There appears to be no difference in the run times of each macro. The timing function returns about 0.74 seconds for each(+/- slightly on each run). Here's what I used (and subtracted A1 from A2, and A5 from A6) :- Sub Macro1()