Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2005
    Posts
    31

    Unanswered: Complicated Query

    I'm totally new to excel so please bare with me, I have an access db which I edit using excel, what I want to do is create a query that takes the price from one column and takes 25% off it if there is 2 units in stock, 30% off if there is 3 units, 40% if there is 4 units in stock and 50% if ther is more than 5 units in stock, the results will then be created in a column called DiscountPrice and the percentage off will will be created in a column called SaleInfo. Is this possible?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Here's a formula to calculate the discount percentage based on having the quantity in column A of your Excel Spreadsheet.

    =1 - IF(A3>5,0.5,CHOOSE(A3,1,0.75,0.7,0.6,0.5))

    Then you can calulate the discountPrice with a formula like this. based on the discount percent formula in column C

    =B3*1-C3

    You can also use the formula with some modifications to bring in the values directly from your SQL Query String however you have it configured.

    One thing to note you are better to use a formula and show the discount percent and discount price in the displayed form/report/spreadsheet and avoid doig the calculations and saving the values in your database tables. This will allow you more flexibility in maintaining the database and adapting to changes in requirements. Say you want to yous a 20% discount instead of a 25%, you just change the formula and calulate off the base value of the price when you run the query.
    Attached Thumbnails Attached Thumbnails ExcelFormula.jpg  
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •