MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Multiple Formulae


Posted by Deon on October 21, 2001 10:35 PM

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
up to 5 horses $12.25 for first
$8.25 for rest

more than 5 1st = $12.25
$8.25 for next 4
$6.95 for rest

I am stumped, can I use a formula, do I need an array?


Posted by Leroy on October 21, 2001 11:32 PM

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))

Posted by Tom Urtis on October 21, 2001 11:47 PM

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?

Posted by Tom Urtis on October 21, 2001 11:59 PM

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

Posted by Leroy on October 22, 2001 12:07 AM

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 ;-)

Posted by Aladin Akyurek on October 22, 2001 12:12 AM

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

Posted by Alan Ball on October 22, 2001 1:44 AM

>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

Posted by Aladin Akyurek on October 22, 2001 1:58 AM

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

Posted by Alan Ball on October 22, 2001 4:23 AM

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

Posted by Alan Ball on October 22, 2001 4:36 AM

PS ...


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

Posted by Aladin Akyurek on October 22, 2001 11:27 AM

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?

Posted by Alan ball on October 22, 2001 3:20 PM

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

Posted by Aladin Akyurek on October 22, 2001 3:58 PM

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()