calculate discounts on lower fees
I have 3 amounts and I need to calculate a discount on the 2nd and 3rd lowest
fees. They may all be the same amount or they may be different. There may only be 1 amount in which case there is no discount. Any suggestions? |
calculate discounts on lower fees
Use the SMALL function
=SMALL(A1:A10,2) etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "aw49" wrote in message ... I have 3 amounts and I need to calculate a discount on the 2nd and 3rd lowest fees. They may all be the same amount or they may be different. There may only be 1 amount in which case there is no discount. Any suggestions? |
calculate discounts on lower fees
Thanks, I get that but how do I get to the next step and figure the discount?
A1 B1 C1 D1 sub tot 1000 2000 500 0 5%dis Now I need to choose the 2nd, 3rd,4th lowest sub totals and apply 5% discount on each and then reduce the subtotals by the discount. I want to ignore the highest and the zero. It is possible for all 4 to be the same amount in which case 3 out of 4 get a discount. Also, if there is only 1 sub tot then no calculation necessary. Any advice? "Bob Phillips" wrote: Use the SMALL function =SMALL(A1:A10,2) etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "aw49" wrote in message ... I have 3 amounts and I need to calculate a discount on the 2nd and 3rd lowest fees. They may all be the same amount or they may be different. There may only be 1 amount in which case there is no discount. Any suggestions? |
calculate discounts on lower fees
Hi
Try =IF(A2=0,A2, IF(COUNT($A$2:$D$2)<2,A2, IF(A2<=SMALL($A$2:$D$2,COUNTIF($A$2:$D$2,"=0")+3), A2*0.95.A2))) -- Regards Roger Govier "aw49" wrote in message ... Thanks, I get that but how do I get to the next step and figure the discount? A1 B1 C1 D1 sub tot 1000 2000 500 0 5%dis Now I need to choose the 2nd, 3rd,4th lowest sub totals and apply 5% discount on each and then reduce the subtotals by the discount. I want to ignore the highest and the zero. It is possible for all 4 to be the same amount in which case 3 out of 4 get a discount. Also, if there is only 1 sub tot then no calculation necessary. Any advice? "Bob Phillips" wrote: Use the SMALL function =SMALL(A1:A10,2) etc. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "aw49" wrote in message ... I have 3 amounts and I need to calculate a discount on the 2nd and 3rd lowest fees. They may all be the same amount or they may be different. There may only be 1 amount in which case there is no discount. Any suggestions? |
All times are GMT +1. The time now is 06:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com