Formula help needed please.

Anx

New Member
Joined
Mar 31, 2002
Messages
22
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Also this formula ..."=[today's date]-[date returned]" only gives me 0, i've set format to general as well.
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top