Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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... |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Limit a sum using discounts | Excel Discussion (Misc queries) | |||
Employing constant arrays to limit nested IF statements. | Excel Worksheet Functions | |||
Nested IF limit or Open parentheses limit | Excel Discussion (Misc queries) | |||
Any way to get around the 240-character line limit on text output? | Excel Discussion (Misc queries) | |||
How do I limit the number of rows in an Excel worksheet. | Excel Worksheet Functions |