Formula to add to running totals

Iron Contributor

Hi,

 

Please assist me with a formula. I have explained what I want in the file.

13 Replies

@A_SIRAT 

 

Are you open to a different approach?

 

I think, to be more specific, you'd be better served by a single transactional table that just begins with  a row of opening inventory levels in the two spots. (That's what you have now as a separate table, which I'm suggesting is adding an unnecessary complication.)

 

With a single transactional table, you can handle transfers, incoming shipments, outgoing, and resulting balances, all in one single database.

 

Yes, I am open to a different approach.

Could you kindly edit the workbook and show me with one table.

@A_SIRAT 

Could you kindly edit the workbook and show me with one table.

I can do that. First let me ask what rules or assumptions have you built into this? For example:

  1. What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter? Or vice versa?
  2. Are there other entities--other stores?
  3. When something is "Issued" what does that mean? Just goes out of your area of concern?
  4. You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000.
    • But to which site does that apply? It clearly isn't a limit applied to "Issued" for example.
    • It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else?
  5. I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going?

 

Finally, are there any other factors that I've not asked about but that you take for granted?

@mathetes 

Could you kindly edit the workbook and show me with one table.

I can do that. First let me ask what rules or assumptions have you built into this? For example:

  1. What is the relationship between Storekeeper and Main Store? Is the former the warehouse for the latter?  Or vice versa? The Main Store is a controlled internal warehouse that issues items to the Storekeeper (Issuing Store)when they have exhausted or are unable to fully issue items to end users. They can only get the minimum supplied quantity needed to fill their gap. E.g. If chemical XYZ is delivered by the supplier(purchases) in 1ltr packaging and the issuing store have a shortage of 2500ml, then they will get 3ltrs full and be left with 500ml.
  2. Are there other entities--other stores? No other except Main store as explained above and the Storekeepers (Issuing Store)
  3. When something is "Issued" what does that mean? Just goes out of your area of concern? Yes. Let me add a point. The end users will request for items from the storekeepers. The Storekeeper will check their balances and if it they have enough stock, they will issue. If they have a shortage, then they will request from the Main Store. For example, End users will request for 200ml of chemical XYZ while the storekeepers ((Issuing Store) have 100ml in stock. To cover the shortage of 100ml, they will get a full 1ltr bottle (1000ml) from the Main store which is the minimum quantity supplied by external suppliers(Purchases). They will issue the 200 and be left with 900ml till further request in future.
  4. You have one column headed "Minimum Transfer Size" for example; I can safely assume that means that when a transfer of stock is called for, the minimum is 1,000. This one is dictated by the quantities supplied to the Main Store by external suppliers. A supplier could supply his products in 1ltr packaging, or 20ltr packaging. Etc.  So when a transfer is called for, then the Main Store will issue what is required in the original packaging quantity. In the above example, the issuing store needed 100ml but because the Main store has 1ltr bottles as the smallest unit, they will issue it in full while the storekeepers will keep their remaining balance. Hope to have clarified.
    • But to which site does that apply? It clearly isn't a limit applied to "Issued" for example. There is no limit to issuance as long as they have enough stock.
    • It appears to apply to transfers from MainStore to Storekeeper: is that correct? Does it apply anywhere else? It does not apply elsewhere. Mainstore is there as a controlled warehouse  and transfers to the storekeepers only when they have shortages.
  5. I see under "Issued" various quantities...I presume that just covers shipments out, but where are they going? This is a farm and the requests are either chemicals or fertilizers done on a daily basis. Once released, it goes to be used in their activities. I used one item as an example but they issue tens of items every day.

 

Finally, are there any other factors that I've not asked about but that you take for granted? For me, what is important is to know the running totals of each items per store and the quantity that needs to be requested from the Main store by the storekeepers.

I hope to have shed more light.

@A_SIRAT 

 

I hope to have shed more light.

You did for sure. But your responses also raised a few more questions. (In order to have a really robust system/spreadsheet, it is important to spell out all the details.)

  1. Your example refers to a Minimum Transfer Size of 1000. I'm thinking now, however, that this limit applies to Item Code C3434/Item Name 30 Col. Are there other Minimum Transfer Sizes for other products? If so, give me a few more examples to work with.
  2. Your new set of data refers to quantities as liters and milliliters. So we're not just dealing with abstract numbers; in these cases we're dealing with liquid volumes. That's important to note. Are there other products (as I've asked above) with other types of measurements, e.g., weight, or length? If so, would you spell those out in addition to the examples I've asked for above.  
    • [I'm picturing a table here that spells out these details, a table that helps manage the transactions. Your initial example may have been overly simplistic.]
  3. In your response to question #5, you include the phrase "This is a farm..." but it's not clear to me whether the "the farm" is the Storekeeper, or a facility IN the farm, or something else. Could you clarify.

 

That's all for now. Thanks.

@mathetes 

 

You did for sure. But your responses also raised a few more questions. (In order to have a really robust system/spreadsheet, it is important to spell out all the details.)

  1. Your example refers to a Minimum Transfer Size of 1000. I'm thinking now, however, that this limit applies to Item Code C3434/Item Name 30 Col. Are there other Minimum Transfer Sizes for other products? If so, give me a few more examples to work with. Yes there are..please see below.

Item Code

Item Name

Transfer Size

C3434

30 Col

1000ml

C3069

1.8 EC

1000ml

C3990

DS 4 EC

5000gms

C3264

R Gold

2500gms

C3395

240SC R

1000gms

C3241

50 WG

1000ml

 

  1. Your new set of data refers to quantities as liters and milliliters. So we're not just dealing with abstract numbers; in these cases we're dealing with liquid volumes. That's important to note. Are there other products (as I've asked above) with other types of measurements, e.g., weight, or length? If so, would you spell those out in addition to the examples I've asked for above.  I have provided a few in the table
    • [I'm picturing a table here that spells out these details, a table that helps manage the transactions. Your initial example may have been overly simplistic.] :smiling_face_with_smiling_eyes:
  2. In your response to question #5, you include the phrase "This is a farm..." but it's not clear to me whether the "the farm" is the Storekeeper, or a facility IN the farm, or something else. Could you clarify. This is an agricultural farm and the chemicals or fertilizers being used in the farm are requested for by the end users every day from the storekeeper. The storekeeper will receive the requests, check if they have sufficient quantities in stock and after some approval formalities, issue them. If they have shortages, they prepare a summary of what they need (difference) from the Main store and give to their superior who will release the items as explained earlier.

Thank you and hoping not to have thrown a spanner in the works.

Thanks. I will work on this. It may well take a day or two, and if I have more questions I'll come back to you. But I think you've told me what I need to know.
By the way, what version of Excel are you working with? I would like to be able to employ the most current functions, which means I hope you have Excel 2021 or newer, or a subscription to Microsoft 365.
I have Office 2021.

@A_SIRAT 

 

This has turned out to be a bit more complicated (for me) than I'd anticipated. Part of it is that my on-line connection is giving me trouble this morning.

 

Take a look at what's here and see if it does at least some of what you're wanting to do. I know the formulas can be made more elegant (using LET, for example), but I wanted first to check with you to see if it was even going in the right direction. 

 

Based on your original sheet, the formulas you'd written there, it's clear that you're pretty adept with some pretty complicated formulas, and with data validation and other semi-advanced features. Maybe you can just build on what I've started here, but by all means come back with any questions. I think my main point was that you should be able to have it all managed with one main "transactional" table. There is a behind-the-scenese table with info on each product, but other than that.....from Initialization to all the other transactions that take place, it can all be handled on one table, with running totals up-to-date.

@A_SIRAT 

 

A much improved version (in that the formulas now take advantage of the LET function, to be more readable). And they're more consistent. there some documentation of the formulas, but if you have questions about them, feel free to come back with those questions.

@mathetes 

thank you for the file and introducing me to the LET function.

 

Would it be possible to add the "refill" quantities suggested in column K ( through a formula) to the running totals per store ( add to Column 1 and transfer from j) without the need to manually do this. When I have 15 different items that need a refill, I will be required to manually add 30 new rows to update the stock.

@A_SIRAT 

 

If LET is new to you, I recommend some research. Here are several resources to learn more about it.

 

Would it be possible to add the "refill" quantities suggested in column K ( through a formula) to the running totals per store ( add to Column 1 and transfer from j) without the need to manually do this. When I have 15 different items that need a refill, I will be required to manually add 30 new rows to update the stock.

 

A formula to do that would be tricky, if possible; it would surely be possible via a macro (which I don't write) BUT, anyway, as your consultant on this (for the time being at any rate), I would strongly advise against trying to accomplish that anyway. 

  1. For one thing, the Transfer transaction takes care of both column I and J, adding to the one, subtracting from the other, already, so even in the scenario you describe, you'd only have to add 15 new rows, not thirty.  (Does it happen that often that there are multiple re-orders necessary in the same day?!)
  2. More importantly, making it "automatic" would lead to you (or the human beings responsible for these transactions) becoming being more passive, less engaged in managing the situation. One of the reasons I wrote this as I did, just highlighting the need for a refill after a threshold has been crossed, is to call attention to a need, to prompt the human user to take responsibility for a decision or two (or more):
    • Do we need to start stocking larger quantities of this product in Storekeeper? (i.e., change the "minimum")?
    • Do we need more this time--seasonal requirement perhaps--than the normal, but can leave the minimum as is?

Of course, I'm not really your consultant, and I'm certainly not there on the scene using the spreadsheet, but those are my thoughts. I would definitely recommend living with it as it is for a while, though, to see if there are other refinements you'd want to see.