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