I sent this out late in the evening, and it seems a problem slipped past my quality control. last minute updates to the formulas messed something up, so please be aware the results are a bit incorrect.
I will fix it when I get a chance and repost. It should be in the next couple days. Asa From: Asa Rossoff [mailto:a...@lovetour.info] Sent: Tuesday, May 15, 2012 3:19 AM To: 'excel-macros@googlegroups.com' Subject: RE: $$Excel-Macros$$ Formula for discount of third item bought by a customer Hi Robinson, To my way of looking at it, the problem is rather complicated, particularly since the invoice does not have a separate row for each individual item (but rather multiple quantities per row). For me the simplest way to try to solve the problem would be to use a VBA macro, but as a challenge, I decided to try to solve your query using formulas. I took the liberty of further complicating the problem by assuming it might be possible for zero or negative quantities to appear on the invoice, and if they do, that no discounts should be applied to them. I worked up a solution file for you. I rely heavily on Defined Names (named formulas). Only one named formula need be referenced from the worksheet, and the formula is called Discount. It goes in a column on the invoice. That one named formula refers to other named formulas in order to break the calculation into interim steps. This greatly simplifies the calculation, making it easier to understand, easier to update in the future if need be, and also allows Excel to optimize how it solves the calculation, making it more efficient. If the named formulas were "expanded" out onto the worksheet, the result would be a formula of well over 2,000 characters long on each row of the invoic. Rather daunting to maintain. Another thing I did as part of my solution was to convert your invoice table range into an Excel Table (called a List in older Excel versions). This automatically creates a dynamically expanding/contracting named range for the invoice, should the number of rows change. It also allows the use of structured references, a fairly simple way of referring to columns of the table (and other parts of the table, like the header row) by name. Those ranges are all dynamic in size as the size of the Table changes, and as with names in general, formulas containing them are easier to read than formulas containing traditional range references. The first row of the worksheet has an input parameter cell where you can change the number of items per discount group, if desired. The formula as designed has a few, probably minor, limitations: . the maximum of all quantities combined is equal to the number of rows that exist on the worksheet (In Excel 2007+, over 1 million); . if you use extremely small fractional quantities, especially on an invoice that includes rows with zero and negative quantities, and has a huge number of lines on the invoice, it could cause some discounts to be applied to the wrong lines or items. someday if Excel supports > 9,999,999 rows and your total of all quantities surpasses that number, the issue would get compounded (the basic rule for absolute safety is, I think: Take the number of decimal points to the right of the decimal point in your smallest quantity, add that to the number of digits in one less than the number of rows on your invoice, and subtract one. If the result is no more than 8, you're safe. With 100 rows on your invoice, for example, this safely permits quantities as small as 0.000001. . you can only have a whole number of items per discount group (no applying a discount to every 1.5 items). Hope this resolves your query. Please let me know what you think and if this was helpful. If anyone else has has any ideas on how to simplify or improve the calculation, I'm interested. File attached, or download @ https://docs.google.com/open?id=1A1SfcsfXsS7VJq7Rs7pZD16nWVYaHxb3JCS2xKyvf54 (File>Download). Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Robinson Boreh Sent: Tuesday, May 08, 2012 7:52 AM To: Robinson Boreh; excel-macros@googlegroups.com Subject: RE: $$Excel-Macros$$ Formula for discount of third item bought by a customer Hi, Please help Sent from my Windows Phone -----Original Message----- From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Robinson Boreh Sent: Sunday, May 06, 2012 7:27 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Formula for discount of third item bought by a customer Thanks. The logic is for every three items, the third is free nd the free should be the lowest price. The items should be arranged in order they are bought in batches of three. See attached On 6 May 2012 16:39, dguillett1 <dguille...@gmail.com> wrote: A VERY simple formula for the 1st question. =LARGE($d$2:$c$4,1)+LARGE($d$2:$c$4,2) More info on the logic with examples of the 2nd Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Robinson Boreh <mailto:rbo...@gmail.com> Sent: Sunday, May 06, 2012 7:46 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Formula for discount of third item bought by a customer Dear Members, I need help in coming up with an excel sheet to calculate the cost of sales i make. Here is my dilemma: If a customer buys three items priced differently, then the third item that costs the least is given as a discount to the client and only charged for 2 items that cost the highest Example Item Qty Cost Amount x01 1 34 34 x02 1 25 25 x03 1 30 30 Amount to pay 64 Discount 25 If a client buys more than three items, then discount is based on batch of three bought in a sequential order. Please assist. Regards, Boreh -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ---------------------------------------------------------------------------- -------------------------- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
