See the attached spreadsheet. Book1.xlsx. I am using Excel 2010 and have data in 5 columns Tag, Part, Date, Qty, and Cost. In a separate column I need to Compute a bill based upon the following logic:
If less than 3 tags have been filed for a particular part number during any 6 month time period then now additional bill is needed.
An additional cost needs to be calculated for any period of time that tags were filed for a particular part at a rate of 3 or more tags every 6 months. Once this cost period has been established, the additional cost calculation is as follows:
The additional cost will be the lesser of $1000 or Qty*Cost*25% over each separate cost period.
I am at a loss as to how to start making this calculation and any help would be awesome.
Bookmarks