calculate discounts on lower fees

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

aw49 - 06 Nov 2006 05:17 GMT

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?

Reply to this Message

Bob Phillips - 06 Nov 2006 11:47 GMT

Use the SMALL function

=SMALL(A1:A10,2)

etc.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> 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?

Reply to this Message

aw49 - 06 Nov 2006 15:10 GMT

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?

> Use the SMALL function
>
[quoted text clipped - 7 lines]
> may
> > only be 1 amount in which case there is no discount. Any suggestions?

Reply to this Message

Roger Govier - 06 Nov 2006 17:06 GMT

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)))

Signature

Regards

Roger Govier

> Thanks, I get that but how do I get to the next step and figure the
> discount?
[quoted text clipped - 27 lines]
>> > only be 1 amount in which case there is no discount. Any
>> > suggestions?

Reply to this Message