limit a value 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...

One way:

=E9*F9*MAX(1-$G$24,70%)


or, equivalently

=E9*F9*(1-MIN($G$24,30%))


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.

Thanks the first one didn't work, but the second did. Sorry for the
duplicated question but I thought I had logged the question under General
Questions and so coldn't find it, so re-logged it.

"JE McGimpsey" wrote:

One way:

=E9*F9*MAX(1-$G$24,70%)


or, equivalently

=E9*F9*(1-MIN($G$24,30%))


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.

Hmmm... the first and the second return identical results as long as G24
is a number. But glad you got it working.

In article ,
"Duncan, UK" wrote:

Thanks the first one didn't work, but the second did.

G24 is formatted as a Percent, maybe that is way. But the second worked
thanks.

"JE McGimpsey" wrote:

Hmmm... the first and the second return identical results as long as G24
is a number. But glad you got it working.

In article ,
"Duncan, UK" wrote:

Thanks the first one didn't work, but the second did.