Hi, I have the 2003 version of Excel and was wondering if anyone knew how to create a formula that adds figures into several cells at once?



Basically, I am trying to create a sheet that calculates stock levels and profit/loss each day. The confusion comes in where I have a special offer, as it takes away stock in several of the other rows.

For example, say I have Item 1, 2, 3 and then the offer. The offer has one of all these items. If someone buys all of the items through the offer, I want it to take away stock from each item's stock levels.

Sales Stock left
Item 1 3 7-2
Item 2 4 6-2
Item 3 6 4-2
Offer 2 N/A

The -2s are what I want the formula to do as I have sold 2 of the offer. How can I do this?

Hope this makes some sense!

Thanks, Rob

EDIT: oh dear, it seems to have messed up my table. I have colour coded it- each colour corresponds to a different column

Hi,
could you please attach a sample sheet for same days following each other, to make it easier to understand how your data is structured.

Kind regards
Bjorn

Hi, sorry for the very late reply. I have attached a sample to this post

Attached Files

Profitstock levels.xls (13.5 KB, 20 views)

Hi,
Since I don't know if you have hundreds of items, a huge number of different offers etc, I base my solution on what you have sent.

The quick and easy solution is to change cell C2 from "=H8-B2" into "=H8-B2-$B$5". Copy this formula down to C3 and C4.

Kind regards
Bjorn

Ah yes, that works thanks! I'm guessing if there were more items, and I only wanted to include the first few for example, I could use the same formula but change it only for the rows included in that offer?

Hi,
I am happy that it worked for you.

Note: Having different formulas on different rows (in the same column) is something I would strongly recommend to avoid. Sooner or later there will be a mistake, and it is also very difficult for others (and you) to see how it works.

Depending on how your data is structured, like how many different offers do you have, are there overlapping offers (two different offers connected to the same part), how many part numbers do you have in total (with and without offers) there are different solutions.

If you give me more information I can recommend the best solution for your situation. The more exact info you give, the better answer you will get

Regards
Bjorn