Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Discount pricing for number of classes taken with different discou

I am working on a pricing sheet for regular and premier classes. Regular
classes are $9/class and Premier are $14/class. If the customer takes classes
they would get a 10% discount off the 2nd class, if they take 3 classes they
get 10% off the 2nd and 20% off the 3rd, 4th, 5th, etc. If cust take 3
classes and 2 are Premier then it would be something like this,
Pre+Pre-(Pre*.10)+reg-(reg*.20). They pay the premier price 1st, the lower
discount on the 2nd premier class and then the 20% off the regular class.
But when you mix things up thats when I get really confused. I have too many
if/and statements, there has got to be a better way.

Rates Class Type
Customer Type Regular Premier
Regular Rate (REG) $9.00 $14.00
Min Rate $6.75 $10.00
Number of Weeks 8
Regular Rate Discounts for Multiple Classes (REG)
Disc. for 2 10.00% 10.00%
Disc. for 3+ 20.00% 20.00%
Name Reg 1 Reg 2 Reg Cls Prem1 Prem2 Prem Cls Total Name 1 2 1 3 2 1 3 6
Name 2 2 0 2 1 1 3

So for client 1 taking 3 regular classes and 3 premier classes it would be
56.65, or 14+11.90+10.50+(3*6.75)
For Client 2 taking 2 reg classes and 1 premier it would be 28.67 or
14+7.92+6.75.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default Discount pricing for number of classes taken with different discou

If you are dealing with too many ifs...may be time to consider using a Pivot
Table:

http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe B." wrote:

I am working on a pricing sheet for regular and premier classes. Regular
classes are $9/class and Premier are $14/class. If the customer takes classes
they would get a 10% discount off the 2nd class, if they take 3 classes they
get 10% off the 2nd and 20% off the 3rd, 4th, 5th, etc. If cust take 3
classes and 2 are Premier then it would be something like this,
Pre+Pre-(Pre*.10)+reg-(reg*.20). They pay the premier price 1st, the lower
discount on the 2nd premier class and then the 20% off the regular class.
But when you mix things up thats when I get really confused. I have too many
if/and statements, there has got to be a better way.

Rates Class Type
Customer Type Regular Premier
Regular Rate (REG) $9.00 $14.00
Min Rate $6.75 $10.00
Number of Weeks 8
Regular Rate Discounts for Multiple Classes (REG)
Disc. for 2 10.00% 10.00%
Disc. for 3+ 20.00% 20.00%
Name Reg 1 Reg 2 Reg Cls Prem1 Prem2 Prem Cls Total Name 1 2 1 3 2 1 3 6
Name 2 2 0 2 1 1 3

So for client 1 taking 3 regular classes and 3 premier classes it would be
56.65, or 14+11.90+10.50+(3*6.75)
For Client 2 taking 2 reg classes and 1 premier it would be 28.67 or
14+7.92+6.75.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Discount pricing for number of classes taken with different di

I don't know if that will work for what I am trying to figure out? I have
never created one. Thanks for responding though.

"ryguy7272" wrote:

If you are dealing with too many ifs...may be time to consider using a Pivot
Table:

http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe B." wrote:

I am working on a pricing sheet for regular and premier classes. Regular
classes are $9/class and Premier are $14/class. If the customer takes classes
they would get a 10% discount off the 2nd class, if they take 3 classes they
get 10% off the 2nd and 20% off the 3rd, 4th, 5th, etc. If cust take 3
classes and 2 are Premier then it would be something like this,
Pre+Pre-(Pre*.10)+reg-(reg*.20). They pay the premier price 1st, the lower
discount on the 2nd premier class and then the 20% off the regular class.
But when you mix things up thats when I get really confused. I have too many
if/and statements, there has got to be a better way.

Rates Class Type
Customer Type Regular Premier
Regular Rate (REG) $9.00 $14.00
Min Rate $6.75 $10.00
Number of Weeks 8
Regular Rate Discounts for Multiple Classes (REG)
Disc. for 2 10.00% 10.00%
Disc. for 3+ 20.00% 20.00%
Name Reg 1 Reg 2 Reg Cls Prem1 Prem2 Prem Cls Total Name 1 2 1 3 2 1 3 6
Name 2 2 0 2 1 1 3

So for client 1 taking 3 regular classes and 3 premier classes it would be
56.65, or 14+11.90+10.50+(3*6.75)
For Client 2 taking 2 reg classes and 1 premier it would be 28.67 or
14+7.92+6.75.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Discount pricing for number of classes taken with different di

I tried to work with a pivot table and couldnt get it to work. Do you have a
suggestion?

"Joe B." wrote:

I don't know if that will work for what I am trying to figure out? I have
never created one. Thanks for responding though.

"ryguy7272" wrote:

If you are dealing with too many ifs...may be time to consider using a Pivot
Table:

http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe B." wrote:

I am working on a pricing sheet for regular and premier classes. Regular
classes are $9/class and Premier are $14/class. If the customer takes classes
they would get a 10% discount off the 2nd class, if they take 3 classes they
get 10% off the 2nd and 20% off the 3rd, 4th, 5th, etc. If cust take 3
classes and 2 are Premier then it would be something like this,
Pre+Pre-(Pre*.10)+reg-(reg*.20). They pay the premier price 1st, the lower
discount on the 2nd premier class and then the 20% off the regular class.
But when you mix things up thats when I get really confused. I have too many
if/and statements, there has got to be a better way.

Rates Class Type
Customer Type Regular Premier
Regular Rate (REG) $9.00 $14.00
Min Rate $6.75 $10.00
Number of Weeks 8
Regular Rate Discounts for Multiple Classes (REG)
Disc. for 2 10.00% 10.00%
Disc. for 3+ 20.00% 20.00%
Name Reg 1 Reg 2 Reg Cls Prem1 Prem2 Prem Cls Total Name 1 2 1 3 2 1 3 6
Name 2 2 0 2 1 1 3

So for client 1 taking 3 regular classes and 3 premier classes it would be
56.65, or 14+11.90+10.50+(3*6.75)
For Client 2 taking 2 reg classes and 1 premier it would be 28.67 or
14+7.92+6.75.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default Discount pricing for number of classes taken with different di

couldnt figure out the pivot table, so i just created a table based on the
calculations and then a vlookup and match function which works perfectly.

"ryguy7272" wrote:

If you are dealing with too many ifs...may be time to consider using a Pivot
Table:

http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.babeled.com/2008/07/18/ex...-manipulation/

HTH,
Ryan--


--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe B." wrote:

I am working on a pricing sheet for regular and premier classes. Regular
classes are $9/class and Premier are $14/class. If the customer takes classes
they would get a 10% discount off the 2nd class, if they take 3 classes they
get 10% off the 2nd and 20% off the 3rd, 4th, 5th, etc. If cust take 3
classes and 2 are Premier then it would be something like this,
Pre+Pre-(Pre*.10)+reg-(reg*.20). They pay the premier price 1st, the lower
discount on the 2nd premier class and then the 20% off the regular class.
But when you mix things up thats when I get really confused. I have too many
if/and statements, there has got to be a better way.

Rates Class Type
Customer Type Regular Premier
Regular Rate (REG) $9.00 $14.00
Min Rate $6.75 $10.00
Number of Weeks 8
Regular Rate Discounts for Multiple Classes (REG)
Disc. for 2 10.00% 10.00%
Disc. for 3+ 20.00% 20.00%
Name Reg 1 Reg 2 Reg Cls Prem1 Prem2 Prem Cls Total Name 1 2 1 3 2 1 3 6
Name 2 2 0 2 1 1 3

So for client 1 taking 3 regular classes and 3 premier classes it would be
56.65, or 14+11.90+10.50+(3*6.75)
For Client 2 taking 2 reg classes and 1 premier it would be 28.67 or
14+7.92+6.75.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
charting # Training classes babs Charts and Charting in Excel 0 November 4th 07 04:33 PM
Excel Classes alish Excel Discussion (Misc queries) 3 October 30th 07 02:03 PM
data classes Huidekoper Excel Discussion (Misc queries) 1 April 21st 06 04:22 PM
Counting Classes MillerK Excel Discussion (Misc queries) 0 March 10th 06 04:32 PM
Anyone know good excel classes? ph8 Excel Discussion (Misc queries) 2 December 16th 05 04:16 PM


All times are GMT +1. The time now is 04:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"