Formula help needed please.

[Anx]

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

[jrnyman]

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

[s-o-s]

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

[Anx]

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

[Anx]

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