Can not calculate daily percent change using excel functions

Hey Guys,

I am new to this forum. I see lot of active people providing help on various topics. Here is one I need help with.

I want to calculate percent change on a daily basis. Please see the excel sheet attached.
There are two product lines A and B. There are 4 machines in total. Each product line is running on each machine every day. There is one more variable. The quantities are produced and recorded for different Production Lots. In my example I have recorded only two here. The dates are working days excluding holidays and week ends.

If I want to find out percent change of quantity produced for product line A on machine 1 on 09/20/2010 for the lot 09/30/2010, how do I put it in excel functions and formula? I am using Excel 2007.

Your help will be highly appreciated.

Regards,

Atul

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

EDITED THIS TIME ONLY

Originally Posted by atul_stock2008

I want to calculate percent change on a daily basis...There are two product lines A and B. There are 4 machines in total. Each product line is running on each machine every day. There is one more variable. The quantities are produced and recorded for different Production Lots. In my example I have recorded only two here. The dates are working days excluding holidays and week ends.

If the no. of lots per machine per product line is also consistent on a daily basis and the data (per your sample) is ordered by date, product line, lot date and machine then it should simply be a case of using relative references, no ?
(ie the same number of lines separate prior & current for each transaction)

The above, using your sample:

Please Login or Register  to view this content.

First off, on an aside, no need to quote my post in your reply (clutters your thread & the board).

I confess I'm not sure I really follow... I based my suggestion on your sample.

In your sample it follows that the "prior" value for any given row is 16 rows above given there are 16 transactions per day and those 16 transactions are listed in the same order every day.

To illustrate by example...

Row18:
To determine the "prior" quantity for the value associated with Product A on the 20th Sep where Lot date is 30th Sep and Machine is 1 we can go up 16 rows to row 2.

Row34:
To determine the "prior" quantity for the value associated with Product A on the 21st Sep where Lot date is 30th Sep and Machine is 1 we can go up 16 rows to row 18.

Row50:
To determine the "prior" quantity for the value associated with Product A on the 22nd Sep where Lot date is 30th Sep and Machine is 1 we can go up 16 rows to row 34.

ie the pattern is consistent and will hold true of all other combinations (row 19 relates to 3 as 35 relates to 19 and 51 to 35 etc...)

If in reality your data is not that consistent I would suggest posting a sample that better reflects the real situation you are faced with.

If your data is that consistent then given you mention you illustrated only two "Lots" you will need to adjust the 16 row offset (Products [2] * Machines [4] * Lots [2]) based on however many rows you have per date (Col B)

So to be clear - are you saying you can not sort your data by any means ?

Sorting data (on big sets) will generally lead to much better calculation performance as more efficient techniques can be used

If you can't sort your data you will be looking at a relatively expensive approach I'm afraid.

Why have you added more unnecessary wording oi the title after I took the trouble to edit it to comply with the Forum Rules??????