The "over 49 " gave me the clue. I should have used ROUNDDOWN instead of
ROUND:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUNDDOWN(A1-100,-2)+MOD(A1,100)*(INT(A1/100)*0.25+2))

I post it for the record if for no other purpose and the fact that it is not
limited to any specific number.

By the way RD your formula works correctly up to 600.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Yes RD that works and it points out that there is an error in my formula
whenever the 10's and units go over 49 - too little testing!. It is much
more elegant too!

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk


"RagDyeR" wrote in message
...
Try this:
Up to 500 items @ 0.25 increase per 100:

=SUMPRODUCT((A1{0,100,200,300,400,500})*(A1-{0,100,200,300,400,500})*{2,0.2
5,0.25,0.25,0.25,0.25})

--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

"Sandy Mann" wrote in message
...
wrote in message
ups.com...

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.


I don't think it is what the OP wants, (or if it is he has a very
generous
employer), but just for the fun of it:

=MIN(A1,100)*2+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+M
OD(A1,100)*(INT(A1/100)*0.25+2))

increases by 0.25 for each 100 over 100.

or with explanations included:

=N("Calculate 1st 100")+MIN(A1,100)*2+N("Calculate all other
100's")+(A1100)*((INT(A1/100)*((A1100)*0.125)+2)*ROUND(A1-100,-2)+N("Calcu
late
remaining 10's & units items")+MOD(A1,100)*(INT(A1/100)*0.25+2))
--
Regards


Sandy

Replace@mailinator with @tiscali.co.uk


wrote in message
ups.com...
"Henry" wrote:
Not clear what you want. Sales of 101 units:
Either 100@ $2.00 +1 @ $2.25 =$202.25
Or 101 @ $2.25 = $227.25

Dan Lieberman wrote:
First option
[....]
100 units @ $2.00
101- 200 units @ $2.25
201 - 300 units @ $2.50

So what you mean to say is: $2.00 for the first 100,
$2.25 for the second 100, and $2.50 for the third 100.

What about the fourth 100, etc?

If you mean: $2.50 for any number over 200, you could
use the following formula:

2*min(a1,100) + 2.25*max(0,min(a1-100,100)) + 2.50*max(0,a1-200)

If you mean: an additional $0.25 for each 100 over 100,
I think you need a VBA macro with a loop.

Alternatively, if there is a reasonable limit (e.g,
"no one could sell more than 500"), you could extend
the formula above. For example:

2*min(a1,100) + 2.25*max(0,min(a1-100,100))
+ 2.50*max(0,min(a1-200,100)) + 2.75*max(0,min(a1-300,100))
+ 3.00*max(0,a1-400)

Note: This pays $3.00 for any number over 400.