Price decrease when number goes over a certain point

[smetcalfe]

Hi there

Im a novice with Excel and would luv some help if possible guys?

Probably something simple not sure if this should be in formula forum?

Heres my problem

Example

If im selling apples at £0.50p each. value of apple in cell x qty = Price of apples

But if someone purchases over 12 apples the value of apple reduces to £0.42p. How can i put that into a formula?

Also another similar problem i think?

If someone buys 15 apples they get (12 in a box of apples) 1 box and 3 single apples so 12 @ £0.42p & 3 @ £0.50p

How can i write a formula so when i type in number of apples it works out the cost

Many Thanks

Much Appreciated

[Moo the Dog]

You could try this (if number of apples sold is in A1:

=IF(A1>=12,12*0.42+MOD(A1,12)*0.5,A1*0.5)

- Moo

[Pepe Le Mokko]

First scenario

Please Login or Register  to view this content.

Second one

Please Login or Register  to view this content.

[smetcalfe]

You could try this (if number of apples sold is in A1:

=IF(A1>=12,12*0.42+MOD(A1,12)*0.5,A1*0.5)

- Moo

Moo

Thanks you are a star

What would be the formula for the 1st example (if number of apples sold is in A1)

Many Thanks

[Moo the Dog]

The formula above "=IF(A1>=12,12*0.42+MOD(A1,12)*0.5,A1*0.5)" should be changed to the one below so it can account for multiple dozens:
=IF(A1>=12,INT(A1/12)*12*0.42+MOD(A1,12)*0.5,A1*0.5)

For the first example, I think you could get away with this:
=IF(A1>12,A1*.42,A1*.5)

- Moo