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.