Discount Formula

Hello,

I'm trying to create a price worksheet.

An album has a base price. Additional pages can be ordered. If a client orders 2 albums, the second one (including any additional pages ordered) will be 15% off. If they order 3 or more, any additional albums (after the first 2) will be 25% off (including any additional pages ordered).

I'd like to enter the number of albums ordered and the number of additional pages and have the above calculated.

Any help would be appreciated!

Thanks.

Excel Question.jpg

Try this in F3 and copy down:

Formula:

Please Login or Register  to view this content.

Thanks. That looks great, except I only want the discounts to apply to the additional albums.

For example, they ordered 4 of the 5x5 albums and add 2 spreads to them. The first album would be $380+$32=$412, the second would be $412*.85=$350.20, the third and fourth would be $412*.75=$309 for a total of $412+$350.20+$309+$309=$1380.20.

Any idea how to achieve that?

Thanks!

Thanks. That looks great, except I only want the discounts to apply to the additional albums.

For example, they ordered 4 of the 5x5 albums and add 2 spreads to them.
The first album would be $380+$32=$412, the second would be $412*.85=$350.20,
the third and fourth would be $412*.75=$309 for a total of $412+$350.20+$309+$309=$1380.20.

Any idea how to achieve that?

Thanks!

Try

=($B3+$D3*$E3)*IF($C3=1,1,IF($C3=2,1.85,($C3-2)*0.75+1.85))

I hope this is what you had in mind. I inserted a subtotal column D then added that to the additional spreads quantity times price.

So in D3:

Formula:

Please Login or Register  to view this content.

In G3:

Formula:

Please Login or Register  to view this content.

=if(c3<>0,($b3+$d3*$e3)*if($c3=1,1,if($c3=2,1.85,($c3-2)*0.75+1.85)),0)

Originally Posted by JohnTopley

Try

=($B3+$D3*$E3)*IF($C3=1,1,IF($C3=2,1.85,($C3-2)*0.75+1.85))

Originally Posted by BillyRaySpivy

=if(c3<>0,($b3+$d3*$e3)*if($c3=1,1,if($c3=2,1.85,($c3-2)*0.75+1.85)),0)

Awesome! Thanks so much!!

Try

=IF(AND(C3=0,E3=0),0,($B3+$D3*$E3)*IF($C3=1,1,IF($C3=2,1.85,($C3-2)*0.75+1.85)))