-GoBengals- Posted October 15, 2022 Report Share Posted October 15, 2022 So here is what im working with: Part of my job is overseeing a production crew. We manufacture nicotine products. Part of that crews work is making other companies products in our state for tax purposes. SO we buy it by the gallon, so far we have been making small batches as orders come in, this isnt working for sales team, we NEVER have a large order in stock. So new plan is to make ALL of the flavoring as soon as it arrives into final product. SO we took the year to date sales data, we have 2 sizes of bottle, and 5-6 different levels of nicotine per bottle the nicotine level ONLY matters for the sales data, but the bottle size changes the amount of ML of flavoring put in the bottles. Here is my data and spreadsheet so far. I am missing one step. the 100ML bottles take 30ML of flavoring, the 30ml bottles take 12ml of flavoring. So the data at the top of the image here, is just flat out sales data. column A is the variants of the flavor column B is the number of bottles sold of each variant Column C is those added together column D is the percentage of total bottles sold that is for each variant. so for this flavor we sell 37.59% of 3MG then i bring that data down to the bottom section. at the very bottom in cell D21, i put how many gallons we bought, calculating some waste i evened it to 3700 ML per gallon, whether its left in the jug or waste in the machine or spillage, i calculated 85ml of waste per gallon so its even 3700/ml/gal now in cells E11 and E12, i took those numbers which for the other 100ml bottles is bottles x 30 = ml used, and i then devided it by 2.5 since 30ml devided by 2.5 is 12ml, that way the data in E11/12 shows the amount of ML used for that size since its different. so right now i have a 2 step calculator, and i want a one step calculator currently: step 1: put in number of gallons purchased step 2: manually adjust the cell that has 1470 and ir orange colored until its as close to, without going over, the total ML in cell F21. is there an equation, that will make the cell with 1470 in it automatically adjust toa number that takes cell e14 to as close to F21 as possible without going over. so i dont have to keep guessing numbers to get close. something like Cell C8 = max number possible without making cell E14 go higher than cell F21? if that makes sense. I appreciate ANY help. PS: the MG of nicotine subtracts from the base we add the flavoring to not the flavoring so the flavoring is 30ml or 12ml no matter what amount of nicotine is added. in case that was crossing your mind. 🤔 Quote Link to comment Share on other sites More sharing options...

Cricket Posted October 15, 2022 Report Share Posted October 15, 2022 18 hours ago, GoBengals said: So here is what im working with: Part of my job is overseeing a production crew. We manufacture nicotine products. Part of that crews work is making other companies products in our state for tax purposes. SO we buy it by the gallon, so far we have been making small batches as orders come in, this isnt working for sales team, we NEVER have a large order in stock. So new plan is to make ALL of the flavoring as soon as it arrives into final product. SO we took the year to date sales data, we have 2 sizes of bottle, and 5-6 different levels of nicotine per bottle the nicotine level ONLY matters for the sales data, but the bottle size changes the amount of ML of flavoring put in the bottles. Here is my data and spreadsheet so far. I am missing one step. the 100ML bottles take 30ML of flavoring, the 30ml bottles take 12ml of flavoring. So the data at the top of the image here, is just flat out sales data. column A is the variants of the flavor column B is the number of bottles sold of each variant Column C is those added together column D is the percentage of total bottles sold that is for each variant. so for this flavor we sell 37.59% of 3MG then i bring that data down to the bottom section. at the very bottom in cell D21, i put how many gallons we bought, calculating some waste i evened it to 3700 ML per gallon, whether its left in the jug or waste in the machine or spillage, i calculated 85ml of waste per gallon so its even 3700/ml/gal now in cells E11 and E12, i took those numbers which for the other 100ml bottles is bottles x 30 = ml used, and i then devided it by 2.5 since 30ml devided by 2.5 is 12ml, that way the data in E11/12 shows the amount of ML used for that size since its different. so right now i have a 2 step calculator, and i want a one step calculator currently: step 1: put in number of gallons purchased step 2: manually adjust the cell that has 1470 and ir orange colored until its as close to, without going over, the total ML in cell F21. is there an equation, that will make the cell with 1470 in it automatically adjust toa number that takes cell e14 to as close to F21 as possible without going over. so i dont have to keep guessing numbers to get close. something like Cell C8 = max number possible without making cell E14 go higher than cell F21? if that makes sense. I appreciate ANY help. PS: the MG of nicotine subtracts from the base we add the flavoring to not the flavoring so the flavoring is 30ml or 12ml no matter what amount of nicotine is added. in case that was crossing your mind. 🤔 Okay, I've got you. Self-taught Excel user here. I added an intermediate step to make it easier, but the equations for B8 thru B12 could be modified to include the intermediate step. I used the percentages from D1 thru D5, assuming the values in D8 thru D12 are the same, just displayed with different number of decimal places. Equations are included in B8 thru B12, so that Cell C8 is no longer an entry, but is simply the sum of B8 thru B12. Intermediate step (rows 16 & 17) determines how much of the total mls is included at the 30ml rate and how much is included at the 12ml rate. Column F shows Column B data to two decimal places (not practical). Column G shows the data as an integer. Those values are truncated so that 39.83 shows up as 39. In Column J, I added .5 to the values of B so that it will "round up." Using the Column J values gives you more realistic amounts for the mls. As a final check, I recalculated % sales distribution...which are still very close to the original values. Instead of explaining the equations used for each cell, send me an email [Moore530@aol.com] and I will send you the spreadsheet that I created. I enjoyed the challenge...nerd that I am. Cricket 1 Quote Link to comment Share on other sites More sharing options...

-GoBengals- Posted October 15, 2022 Author Report Share Posted October 15, 2022 4 hours ago, Cricket said: Okay, I've got you. Self-taught Excel user here. I added an intermediate step to make it easier, but the equations for B8 thru B12 could be modified to include the intermediate step. I used the percentages from D1 thru D5, assuming the values in D8 thru D12 are the same, just displayed with different number of decimal places. Equations are included in B8 thru B12, so that Cell C8 is no longer an entry, but is simply the sum of B8 thru B12. Intermediate step (rows 16 & 17) determines how much of the total mls is included at the 30ml rate and how much is included at the 12ml rate. Column F shows Column B data to two decimal places (not practical). Column G shows the data as an integer. Those values are truncated so that 39.83 shows up as 39. In Column J, I added .5 to the values of B so that it will "round up." Using the Column J values gives you more realistic amounts for the mls. As a final check, I recalculated % sales distribution...which are still very close to the original values. Instead of explaining the equations used for each cell, send me an email [Moore530@aol.com] and I will send you the spreadsheet that I created. I enjoyed the challenge...nerd that I am. Cricket oh snap, i knew someone would have a clue what i was trying to do. thank you! I will email you now. Thanks again! Ryan Quote Link to comment Share on other sites More sharing options...

## Recommended Posts

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.