Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
I'm not sure this is possible or if I'm even going about it the right way...
but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Hi
With price in column A and discounts in column B, use this formula. Just enter the discount in $ without the minus sign: =IF(B2<1,A2-A2*B2,A2-B2) Regards, Per "Kevin H." <Kevin skrev i meddelelsen ... I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
My choice would be to have a 'Discount Type' col and use an IF statement to
decide whether to multiply or subtract =IF(C1="Yes",A1*B1,A1-B1) Yes indicates percentage and any other value will indicate dollar amount. Also you can check if value in the discount column is less than say 0.20 (assuming 20% is your highest percentage) [value in percentage column would be definitely less than 1 ... if you do not have SUBTRACT discount of less than 1 then you can compare the value to 1 and choose what to do... "Kevin H." wrote: I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Two examples are not a lot to go on, but bear in mind that a
percentage will always be less than 1, whereas presumably if you want to reduce the dollar amount the discount will always be more than $1.00? If this is the case then you can use one column and your formula would be something like this in C2: =IF(B2="",IF(A2="","",A2),IF(B2<1,A2*(1-B2),A2-B2)) Then just copy it down. This allows you to leave the discount column blank if there is no discount, but you can enter 25%, for example, or $2.50 into the discount column and the discounted price should be correct. Hope this helps. Pete On Jan 4, 8:06*pm, Kevin H. <Kevin wrote: I'm not sure this is possible or if I'm even going about it the right way.... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE * * DISCOUNT * *DISCOUNTED PRICE $50.00 * **.1 $50.00 * *-10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE * *% OFF * * $ OFF * DISCOUNTED PRICE $50.00 * 10% * * * * * * * * * * $50.00 * * * * * * * * *$10.00 Am I making this more complicated than it has to be? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK!
Although I would prefer not to have the extra column, Sheeloo's solution seems the best for me. The other solutions would work, except sometimes the discount in dollars is less than $1.00, which would cause the DISCOUNTED PRICE column to calculate a $0.50 discount as a 50% discount! That's my fault... I should have provided more information... so thanks for the suggestions everyone! "Sheeloo" wrote: My choice would be to have a 'Discount Type' col and use an IF statement to decide whether to multiply or subtract =IF(C1="Yes",A1*B1,A1-B1) Yes indicates percentage and any other value will indicate dollar amount. Also you can check if value in the discount column is less than say 0.20 (assuming 20% is your highest percentage) [value in percentage column would be definitely less than 1 ... if you do not have SUBTRACT discount of less than 1 then you can compare the value to 1 and choose what to do... "Kevin H." wrote: I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
You're welcome.
If your percentage discounts were never more than 50%, you could amend my formula to: =IF(B2="",IF(A2="","",A2),IF(B2<0.5,A2*(1-B2),A2-B2)) in order to allow you to have a discount of $0.50 or more. Hope this helps. Pete On Jan 4, 8:50*pm, Kevin H. wrote: Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK! Although I would prefer not to have the extra column, Sheeloo's solution seems the best for me. The other solutions would work, except sometimes the discount in dollars is less than $1.00, which would cause the DISCOUNTED PRICE column to calculate a $0.50 discount as a 50% discount! That's my fault... I should have provided more information... so thanks for the suggestions everyone! "Sheeloo" wrote: My choice would be to have a 'Discount Type' col and use an IF statement to decide whether to multiply or subtract =IF(C1="Yes",A1*B1,A1-B1) Yes indicates percentage and any other value will indicate dollar amount. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Hi,
Assume that the data below is in range C5:D6. In cell E5, enter the following formula and copy down =IF(CELL("format",D5)="P0",C5*(1-D5),C5-D5) PRICE DISCOUNT $50.00 *.1 $50.00 -10 Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kevin H." <Kevin wrote in message ... I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Thanks for the suggestion! My range is E5:F6, so I adjusted your formula and
inserted it into G5. It seems to handle a flat discount (a dollar amount) just fine, but I get errors when I insert a percentage. E5 and G5 are formatted as currency and F5 is formatted as a general number. Is there something I have to do to F5 to get the formula in G5 to recognize it as a percentage? I tried formatting it as a percentage, but it treated the number as a dollar amount. When I put *.1 as I did in my example, it returns a value error. I actually plan on taking this a step further once I get it figured out. I intend to take the formula results and divide it by SIZE to get a discounted $/UNIT. I provided a simplified example before, but these are two rows showing what I'd like my actual spreadsheet to look like. Percentage discounts are formatted as percents and flat rate discounts are formatted as currency to help you see what I'm trying to do. Of course, I'd rather not have to manually format each cell as a percent or currency if possible. My "dream formula" would be inserted into I5:I6, etc. E F G H I 4 PRICE DISCOUNT SIZE UNIT $/UNIT 5 $1.97 10% 18 fl oz $0.2047 6 $11.33 $1.00 64 loads $0.1614 I really appreciate all the help and quick responses and I think I can make it work based on Sheeloo's suggestion. But, ideally, I'd rather not add the extra column if there is a single "dream formula" that I can insert at the end. I'm hoping your solution works and there's just something that I'm doing wrong when I execute it. "Ashish Mathur" wrote: Hi, Assume that the data below is in range C5:D6. In cell E5, enter the following formula and copy down =IF(CELL("format",D5)="P0",C5*(1-D5),C5-D5) PRICE DISCOUNT $50.00 *.1 $50.00 -10 Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kevin H." <Kevin wrote in message ... I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be? |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Thanks for the follow-up. I can't really say that the discounts won't be more
than 50%. The only guarantee is that the flat-rate (dollar amt) discounts can be any amount and that the percentage discounts will never be 100% off. "Pete_UK" wrote: You're welcome. If your percentage discounts were never more than 50%, you could amend my formula to: =IF(B2="",IF(A2="","",A2),IF(B2<0.5,A2*(1-B2),A2-B2)) in order to allow you to have a discount of $0.50 or more. Hope this helps. Pete On Jan 4, 8:50 pm, Kevin H. wrote: Wow. Thanks for the fast response from Sheeloo, Per Jenssen, and Pete-UK! Although I would prefer not to have the extra column, Sheeloo's solution seems the best for me. The other solutions would work, except sometimes the discount in dollars is less than $1.00, which would cause the DISCOUNTED PRICE column to calculate a $0.50 discount as a 50% discount! That's my fault... I should have provided more information... so thanks for the suggestions everyone! "Sheeloo" wrote: My choice would be to have a 'Discount Type' col and use an IF statement to decide whether to multiply or subtract =IF(C1="Yes",A1*B1,A1-B1) Yes indicates percentage and any other value will indicate dollar amount. Also you can check if value in the discount column is less than say 0.20 (assuming 20% is your highest percentage) [value in percentage column would be definitely less than 1 ... if you do not have SUBTRACT discount of less than 1 then you can compare the value to 1 and choose what to do... "Kevin H." wrote: I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE DISCOUNT DISCOUNTED PRICE $50.00 *.1 $50.00 -10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE % OFF $ OFF DISCOUNTED PRICE $50.00 10% $50.00 $10.00 Am I making this more complicated than it has to be?- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional PRODUCT or SUM
Kevin,
If you really want to confine the discount to one column, then one way you could get it to work would be to enter the percentage discounts as negative and the flat rate discounts as positive (or vice versa). Then the formula can take account of negative and positive amounts in different ways. Hope this helps. Pete On Jan 5, 8:01*am, Kevin H. wrote: Thanks for the suggestion! My range is E5:F6, so I adjusted your formula and inserted it into G5. It seems to handle a flat discount (a dollar amount) just fine, but I get errors when I insert a percentage. E5 and G5 are formatted as currency and F5 is formatted as a general number. Is there something I have to do to F5 to get the formula in G5 to recognize it as a percentage? I tried formatting it as a percentage, but it treated the number as a dollar amount. When I put *.1 as I did in my example, it returns a value error. I actually plan on taking this a step further once I get it figured out. I intend to take the formula results and divide it by SIZE to get a discounted $/UNIT. I provided a simplified example before, but these are two rows showing what I'd like my actual spreadsheet to look like. Percentage discounts are formatted as percents and flat rate discounts are formatted as currency to help you see what I'm trying to do. Of course, I'd rather not have to manually format each cell as a percent or currency if possible. My "dream formula" would be inserted into I5:I6, etc. * * *E * * * * *F * * * * * * * G * * * H * * * * *I 4 * PRICE * DISCOUNT *SIZE *UNIT * *$/UNIT 5 * $1.97 * 10% * * * * * 18 * * fl oz * * $0.2047 6 * $11.33 *$1.00 * * * * 64 * * loads * $0.1614 I really appreciate all the help and quick responses and I think I can make it work based on Sheeloo's suggestion. But, ideally, I'd rather not add the extra column if there is a single "dream formula" that I can insert at the end. I'm hoping your solution works and there's just something that I'm doing wrong when I execute it. "Ashish Mathur" wrote: Hi, Assume that the data below is in range C5:D6. *In cell E5, enter the following formula and copy down =IF(CELL("format",D5)="P0",C5*(1-D5),C5-D5) PRICE * * DISCOUNT $50.00 * **.1 $50.00 * *-10 Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Kevin H." <Kevin wrote in message ... I'm not sure this is possible or if I'm even going about it the right way... but I'd like to know if there is a function (or combination of functions) that I could insert into the DISCOUNTED PRICE column to determine whether to multiply or subtract the DISCOUNT from the PRICE, depending on what is entered in the DISCOUNT column. Some discounts are a percentage and some are dollars off, so I need the spreadsheet to be capable of knowing which type of discount is entered to return the correct DISCOUNTED PRICE result. PRICE * * DISCOUNT * *DISCOUNTED PRICE $50.00 * **.1 $50.00 * *-10 I could also set it up with columns differentiating between a percentage discount and a "dollars off" discount, but how do I set up a DISCOUNTED PRICE function that would know which column has data in it and whether to multiply or subtract the data? PRICE * *% OFF * * $ OFF * DISCOUNTED PRICE $50.00 * 10% $50.00 * * * * * * * * *$10.00 Am I making this more complicated than it has to be?- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom function for Sum Product (Conditional Formula) | Excel Discussion (Misc queries) | |||
Sum product | Excel Discussion (Misc queries) | |||
Conditional Sum Product | Excel Discussion (Misc queries) | |||
need product key | Excel Discussion (Misc queries) | |||
need product key | Excel Discussion (Misc queries) |