Thanks:  0
Likes:  0

1. 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. 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. 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
K - Aged Price
L - Write off amount
M - Unit Price
N - Stock Price

4. 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. Also this formula ..."=[today's date]-[date returned]" only gives me 0, i've set format to general as well.

## User Tag List

#### Posting Permissions

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