Solved

Adjustment required to very complicated Sum

Posted on 2012-03-13
41
240 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi Rorya,

Yes that is correct, can you help me?

Regards

Simon
0
 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
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
Comment Utility
Try this one.
test-1-1.xls
0
 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
>>"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
Comment Utility
Thank you Rorya
0
 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
OK I am going to upload better data for you.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
OK Im on it
0
 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
OK, I think I get that, and I think the attached is correct.
Ink-Stock-Calculator2--1-.xls
0
 

Author Comment

by:Simonrepro
Comment Utility
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
Comment Utility
Fantastic help!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 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

13 Experts available now in Live!

Get 1:1 Help Now