Limit a sum using discounts

[Guest]

I am trying to cap a cell that I use for discounts.
eg the cell could read 35%, but I only want a max of 30% on some products
I am using a cell to input a discount into quotations, this cell is then
used to calculate sums further below eg
g24 = 40%
and I use =(E9*F9)*(1-$G$24) later to get my total BUT I want to be able to
cap some lines with a lower discount eg some products can have up to 40% ,but
others can only go up to 30%.
=MIN(SUM(A1:A10),100) This sum works if the cell is formatted as a general
number, but not if it is formatted as a percentage.

Help please...

 

[Bernard Liengme]

If row 9 represents a product, what in this row tells us it is a limited
discount item?

 

[JE McGimpsey]

See one answer to your post in worksheet.functions.


Please don't post the same message to multiple groups - it just tends to
fragment your answers, and potentially wastes the time of those
answering questions that have already been answered.

For tips on using these groups effectively, see

http://cpearson.com/excel/newposte.htm

 

[Guest]

At the moment the sum looks for G24 where the discount can be set eg 40%, but
I was thinking of using a hidden cell in certain rows where a product can
only have a limited discount eg h56 to say =MIN(SUM(g24),35), then my sum to
calculate the total price (value x quantity - discount) would like
=(E9*F9)*(1-$h$56). This works if G24 & H56 are formatted as numbers, but
not as percentages.

Thanks.

 

[Bob Phillips]

Don't forget, if the cell is formatted as a percentage, it should only
contain .4 or .35, not 40 or 35.

--

HTH

RP
(remove nothere from the email address if mailing direct)