Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Formula help needed please.

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I need help with some a formula please, I have the following data...

    Product code, Qty, Price,Item Code,Comments,Date Returned.

    (The comments header indicates whether the stock is old/new or to be refurbished.)

    What I need to do is to work out the worth of an item depending on date returned/new/refurbished etc.

    If a product is more than 6 months old then - 30% off price.
    If a product is more than 9 months old then - 60% off price.

    If product price is less than 10.00 then write off completely at 6 months


    For 'refurbish' products, a cost of 25% must be deducted from the price. This should be done before calculating whether a product's value is less than 10.00.


    OK I hope i've made this so its understandable

    What I need is a formula that will work this out for me please. (I'm not allowed macros by the way)

    Thanks for any help as I'm really stuck on this.

    Anx


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Milwaukee, WI
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's my suggestion:
    First, create a column just to calculate an "effective price", which considers the refurb factor.The formula would be "=IF([comments]="refurbish",.75*[price],[price])"
    Replace the brackets with the appropriate cell.

    The next thing is to set a cell, say M1, for todays date with "=Today()"

    Then create a column to calculate how old in days an item is. The formula is just "=[today's date]-[date returned]" Make sure you format that column as General.

    Now, a column for value per unit. Here's the formula: "=IF([Days old]>180,IF([effective price]<10,0,IF([days old]<270,0.4*[effective price],0.7*[effective price])),[effective price])"

    Now just make a column for a total and the formula is "=[value per unit]*[Qty]"

    Hope this helps.

    mike

  3. #3
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anx,

    I have made an assumption that the price of refurbishment comes off the price before any other deductions, ie 60%/30% in my solution

    This would be an ideal solution for a user defined function using visual basic, but I have avoided this as you suggest macros are out (Shame)...

    The solution instead uses 6 columns for solving, you could do it with less but then you will have more difficulty following through when changes are required.

    ***
    Data Cells are as follows
    B3 = Formula =Now()
    B5 = Product Code
    D5 = Quantity
    E5 = Item Code
    F5 = Comments (I Suggest Data Validation with a list of Values New, Old, Refurbished)
    G5 = Date Returned

    I5 = Formula =ROUND(($B$3-G7)/30.417,0)
    J5 = Formula =IF(F7="Refurbished",D7*75%,D7)
    K5 = Formula =IF(I7>=9,J7*0.4,IF(I7>=6,J7*0.7,J7))
    L5 = Formula =IF(K7<10,K7,0)
    M5 = Formula =K7-L7
    N5 = Formula =M7*C7

    I have titled columns as follows:
    I - Age in Months
    J - Refurbishment Adjusted Price
    K - Aged Price
    L - Write off amount
    M - Unit Price
    N - Stock Price


    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi this works great..J5 = Formula =IF(F7="Refurbished",D7*75%,D7)

    other than if F7 doesn't= refurbished i get an error, is there a way to find if its NEW also please and add it to that formula?

    Thanks for you help guys

    Anx

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also this formula ..."=[today's date]-[date returned]" only gives me 0, i've set format to general as well.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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