We help IT Professionals succeed at work.

# Adjustment required to very complicated Sum

on
Hi,
The attached spread sheet has been adapted for security reasons but the original one is a lot bigger but the attached does give an example of my problem.

We recycle ink cartridges, so we buy back empty cartridges and then refill with an ink.  So worksheet 'Stock' shows the amount of stock of empty cartridges and ink we have and also finished cartridges.  'Groups' shows what make up a finished cartridge ie ink + empty cartridge.  'Budgets' show the estimated sales.

'Order' is where my problem is - I can enter in dates here for the period I want to purchase in supplies for.  All of the works fine for all of my products appart from where a product is used in more than one finished cartridge.

So in the example attached I want to order enough supplies in to make enough finished cartridges to last me up until the end of May 2012.  In column D is ink type '1' and the answer in cell C6 will be incorrect because the sum is not allowing for the fact that the sum in C4 has already used up all of the available ink, also sometimes I could have the same ink used in three different finished products.

Can someone help me make the current sum allow for this please?

Kind Regards

Simon
test.xls
Comment
Watch Question

## View Solution Only

Chief information Officer

Commented:
Had a look at the formula but its going to take a while to debug and decode all the steps.  its one of the longest most unreadable formula's i've seen in excel.

I think you are approaching the limits of whats possible with formula's based on your current format.

I'd have to break it down into seperate steps, or hidden columns to calculate things one piece at a time.

But i'd recommend looking at the whole product in access instead as its becomming too complicated for excel.

Commented:
Yes it's as if it needs an IF statement saying that if that code of ink has already been checked then for the next one just add all the ink that is required for the next one.

Commented:
I have had a look and I agree with Sudonim, its virtually impossible to model your requirements in a single formula, you need to break it down.

I have made a start in the attached, but unfortunately have a few meetings to go to now, so can't finish it.

Basically, you need to determine what your minimum closing finished stock requirements are for each product, work out the materials that are going to be required to produce the finished stock in the month, and forcase accordingly. The way you are approaching it is all wrong.

Have a look at the attached and see if you can use it as a starting point. I'll be back later.
ee-stock.xls

Commented:
Maybe I did not explain properly.

The sum I already have works, it does work out how many I need of each product for the period I choose.

The problem is that in a couple of cases, over the 150 total different ink cartridges we sell, the finished product uses the same ink as another finished product.  So let's say row 1 has the same ink as row 15, row 1 will calculate correctly but when it calculates row 15 the sum does not realise that ink in stock has already been taken into account for row 1 meaning that row 15 needs to pretend there is '0' zero stock in and calculate what is needed without taking off what's in stock as that has already been taken for row 1.

So basically for 147 of my codes this sum works great but where the ink code is dupliacted there should be the same sum less looing into what's in stock, so the sum just needs reducing in those places.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
This may or may not be on the right lines. I have stripped out the component parts of your formula into separate columns and added a check to the stock column to remove any items that have been used up by rows above. You will need to test it thoroughly as I am not convinced I understood your file layout...
test-1.xls

Commented:
I don't think I am an expert enough to even understand what this one is doing.  What my one does is as follows.

1. Firstly it looks at the budget worksheet to see how many finished cartridges are required for the given period.
2. The result that is displayed for ink in 'Orders' tab is the amount of drums of ink required to fill the budget figures.  To work out drums it uses the columns c and d in groups worksheet.
3.  It then works out how much ink I have in stock from finished cartridges in stock and ink drums in stock combined again using groups worksheet to calculate what this equates to in drums.
4.  It then takes 3 from 2 giving me the result which translates into drums of ink I need to order.

The problem is that this sum only works if there is one ink product code as if there is more than one then it still thinks there is stock but the stock will have already been taken into account for the first one ie when there is more than one finished ink cartridge with the same ink this sum is duplicating the stock situation.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
My formulas produce the same results as yours for your sample when I took a straight sum of the stock as yours did.

I'm not sure what I did after that was correct as I then adjusted the stock formulas on the order sheet to deduct any values in column I for the same item in column H. I suspect what you actually need is to simply discount any stock where there is a similar item above it (column H) that has an order greater than 0?

Commented:
Hi Rorya,

Yes that is correct, can you help me?

Regards

Simon

Commented:
Do you think it may be work me asking someone in the VBA part of experts exchange if they can code something in for me to do this?

Commented:
I don't know whther this is possible but could the sum say if there is more than one instance of a product code then work out the total number of ink drums required required less the stock and put this in one place/
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try this one.
test-1-1.xls

Commented:
Hi Rorya,

Each finished ink cartridge consists of a chip, some ink and an empty cartridge and when I add these new sums to the live spread sheet in the orders worksheet for every single empty cartridge required it shows that i need to order exactly the same quantities of chips and this cannot be correct as i different stock levels of ink and chips?

Regards

Simon
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Since it doesn't do that in the sample workbook, I can't really say what you did or didn't do in your implementation.

Commented:
In cell A3 in orders worksheet is this sum

=MATCH(G3,'Groups '!A\$5:A\$131,0)

Should the answer to this sum be how many times does the value in G3 appear in cells A5 to A131? or is it doing something else?

Commented:
Hi Rorya,

In my live spread sheet for worksheet 'Order', this is the results I get.  Please see the attached, does this look right?

Regards

Simon
test.xls
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
No, it is looking up the position of the first occurrence of G3 on the other sheet.

It occurred to me this morning that your requirement is more complex than I thought - you could have items occurring higher up the list which don't require a reorder so col I is 0) but do use up some of the available stock. I think a reworking will be in order to get all the component parts onto the order sheet. Will try and do that shortly.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
>>"Please see the attached, does this look right?"

No idea since you have removed any trace of what was being looked up!

Commented:
Thank you Rorya

Commented:
Sorry Rorya, I can't put my live data on this site (These are the result values though) but I just wondered because you can see all the numbers in columns B & C and they just seem to be constant in the results ie 1/3/5/7/9 etc, if the numbers were like this surely I would'nt need a sum in these columns and they could just be values?
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Columns A and B are not sums, they are just getting lookup positions. You will note a couple of repeats in column B where presumably you have an ink repeated.

Commented:
Oh yes I see, by the way you will never get a repeat of an ink cartridge or a chip as they are always unique, only the ink could be repeated.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
That's what I had guessed and I assume it is only the second lookup column in the original sample file (the one with the 1s in it) that has the ink codes?

Commented:
Yes that is correct, in my live spread sheet ink is always the 3rd one, so I have only put you sums in on the ink ones but it's not correct so far.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Try this one. I've tried to make all the steps apparent. Note also that I have added a summary column to the stock sheet.
Ordering.xls

Commented:
3.51       6.4       -

All looks good appart from in the to order column I am getting a - sign as per above

Commented:
Hi Rorya,

OK I have checked it firstly I don't see the summary column in the attached?  Also i have the following scenario.

In my live sheet I have an ink which is used in 4 different finished cartridges and from all of the ink in finished cartridges added to all of the drums of ink for the said ink I have a total of 161.4 litres in stock.  To fill my orders I need 220.91 litres meaning I should order 59.51 litres which equates to 2.9755 drums of ink as there are 20litres in each drum.  This sum is telling me I need '-' for all 4 places the said ink shows.

I really appreciate your help her by the way, I know this is above and beyond the call of duty.

Regards

Simon
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Column F on the stock sheet.

I genuinely cannot comment on what you are seeing as I can't see what you can. It seems to work in the sample file you posted. Note though that all my work is done on the assumption that your original formula was correct other than the previous used stock issue.

the "-" means no order required.

Commented:
OK I see the problem:

Budget = 200.40       Stock = 6.4       To Order = 194.00

These are the results in my live sheet, the problem is that the budget figure transaleted to ink required is 200.4 x 0.35 litres of ink in each finished cartridge (You can see this in groups).  Stock is 6.4 x 20 litre drums of ink ie 128 litres of ink so in this case the results should be:

200.4 x 0.35 = 70 Litres required for budget
6.4 x 20 = 128 Litres in stock

= 0 ink required.

My original sum does work but only if there is one cartridge taking one ink.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I am assuming (based on what I think your live sheet looks like) that the factor2 column should apply the relevant scaling for litres/whatever volume.

Commented:
The original sum (Which I think you helped me with, or rather built for me) by using the groups field looks at stock in two places Finished ink cartridges (This is because they contain an amount of ink) and it looks at drums of ink it then combines the two figures and again using groups calculates how many drums of ink I need to order in to complete the amount of finished cartridges in budgets.

It already does this but my problem was that if another finished cartridge took the same ink as another using my original sum it would not allow for the fact that stock or some of the stock had just been taken in to account on another using the same code ie leaving less stock or no stock.

Commented:
Infact if say 4 finished cartridges required in budgets equated to the following:

1 = 10 litres
2 = 5 litres
3 = 10 litres

I would require 25 litres of ink or if 20 litres per drum then 1.25 drums.

If there was 1 drum in stock containing 20 litres and say 1 finished cartridge containing 0.50 litres then I would require 4.5 litres or 0.225 drums.

Because above is a 1,2 & 3 at the moment the sum thinks that I have three times the stock but I only have 1 x the stock.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The last version I posted had the stock formula adjusted to subtract any already counted stock data based on the groupings in the second column of codes. Without much data to go on, I can only say it seemed to work in your sample, unless you can tell me otherwise and explain what the results should have been.

Commented:
OK I am going to upload better data for you.
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The data can be fictitious but we really need to see specific examples of where the original sum formula would fail and what the actual result should be (and why).

Commented:
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I was kind of hoping you would post a version using the layout I posted and explain where it fails and what the result should be. As it is, you haven't really given me any more to go on as to what the problem is.

Commented:
OK Im on it

Commented:
OK here we go!

In the attached worksheet 'Orders' highlighted in red are the ink drums your calculations say I should order in which total 10.50 drums of '1050ink'.

In reality I need the following from budgets:

200 x .35
200 x .68
200 x .775

= 361 Litres of ink or 18.05 drums

I have of this ink in stock:

2 x Drums = 40 Litres
11 x PJ1000CB = 3.85 Litres
11 x CM4000INKCB775 = 8.525 Litres

= 52.525 of ink or 2.62625 Drums

361 - 52.375 = 308.625 Litres so K7+K10+K13 should = 15.43125 Drums in total
Ink-Stock-Calculator2.xls
BRONZE EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
OK, I think I get that, and I think the attached is correct.
Ink-Stock-Calculator2--1-.xls

Commented:
That's the one, thank you ever so much, if I could give you a million points I would.  Cheers!

Commented:
Fantastic help!