Limit a sum using discounts
 

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

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Duncan, UK" wrote in message
...
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...

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



In article ,
"Duncan, UK" wrote:

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

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.

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Duncan, UK" wrote in message
...
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...

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)


"Duncan, UK" wrote in message
...
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.

"Bernard Liengme" wrote:

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

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Duncan, UK" wrote in message
...
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...