Teired rates

Normanbykid

New member
Joined
Aug 25, 2014
Messages
2
Reaction score
0
Points
0
Hi

Just need some help with a formula. We sell 150 units at 0.25 cents then after 150 the price is reduced to 0.10 cents. We can get excel (2013) to calculate 150 at 25 but cannot get a formula to calculate unit price of 10 after 150.

Many thanks
 
Last edited by a moderator:

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try this, assuming the number of units is in A2

=SUMPRODUCT(--(A2>{0;150}),--(A2-{0;150}),{0.25;-0.15})
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
In this case, because you only have two variable rates, this might be simpler

=A2*0.1+MIN(A2,150)*0.15
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Or even

=MIN(A2,150)*0.25 +MAX(0,A2-150)*0.1

:)
 

Normanbykid

New member
Joined
Aug 25, 2014
Messages
2
Reaction score
0
Points
0
Hi

Thanks for your help Bob it worked much appreciated.

:clap2:
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Which did you go for?
 
Top