Solved

Adjustment required to very complicated Sum

Posted on 2012-03-13
41
243 Views
Last Modified: 2012-03-15
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
0
Comment
Question by:Simonrepro
  • 24
  • 15
  • +1
41 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 37714308
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.
0
 

Author Comment

by:Simonrepro
ID: 37714464
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.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 37714501
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
0
 

Author Comment

by:Simonrepro
ID: 37714549
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37715506
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
0
 

Author Comment

by:Simonrepro
ID: 37716122
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37717014
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?
0
 

Author Comment

by:Simonrepro
ID: 37721684
Hi Rorya,

Yes that is correct, can you help me?

Regards

Simon
0
 

Author Comment

by:Simonrepro
ID: 37721725
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?
0
 

Author Comment

by:Simonrepro
ID: 37721765
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/
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37722177
Try this one.
test-1-1.xls
0
 

Author Comment

by:Simonrepro
ID: 37723730
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37723774
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.
0
 

Author Comment

by:Simonrepro
ID: 37723972
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?
0
 

Author Comment

by:Simonrepro
ID: 37723982
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37723983
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37723989
>>"Please see the attached, does this look right?"

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

Author Comment

by:Simonrepro
ID: 37723993
Thank you Rorya
0
 

Author Comment

by:Simonrepro
ID: 37724004
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724024
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Simonrepro
ID: 37724033
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724055
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?
0
 

Author Comment

by:Simonrepro
ID: 37724101
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724177
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
0
 

Author Comment

by:Simonrepro
ID: 37724222
3.51       6.4       -  

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

Author Comment

by:Simonrepro
ID: 37724246
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724280
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.
0
 

Author Comment

by:Simonrepro
ID: 37724298
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724317
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.
0
 

Author Comment

by:Simonrepro
ID: 37724337
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.
0
 

Author Comment

by:Simonrepro
ID: 37724365
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724429
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.
0
 

Author Comment

by:Simonrepro
ID: 37724446
OK I am going to upload better data for you.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724481
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).
0
 

Author Comment

by:Simonrepro
ID: 37724622
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37724642
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.
0
 

Author Comment

by:Simonrepro
ID: 37724648
OK Im on it
0
 

Author Comment

by:Simonrepro
ID: 37724728
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

Answer:

361 - 52.375 = 308.625 Litres so K7+K10+K13 should = 15.43125 Drums in total
Ink-Stock-Calculator2.xls
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37724929
OK, I think I get that, and I think the attached is correct.
Ink-Stock-Calculator2--1-.xls
0
 

Author Comment

by:Simonrepro
ID: 37725426
That's the one, thank you ever so much, if I could give you a million points I would.  Cheers!
0
 

Author Closing Comment

by:Simonrepro
ID: 37725430
Fantastic help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
consolidate from multiple folders 5 39
Adding Text that self adjusts in a Cell 8 31
Problem to With line 4 37
Access Excel export not behaving 2 25
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now