Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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... |
#2
|
|||
|
|||
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... |
#3
|
|||
|
|||
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... |
#4
|
|||
|
|||
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... |
#5
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I limit the number of times an Excel workbook can be opene. | Excel Discussion (Misc queries) | |||
Row Limit in Excel | Excel Discussion (Misc queries) | |||
Can I limit a cell to 72 characters? How? | Excel Discussion (Misc queries) | |||
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) |