Sorting through and rounding data

O

Omer1234

Member
Joined
Apr 12, 2016
Messages
47
Reaction score
0
Points
6
Excel Version(s)
Excell 97-2003
Hi,

Please see spreadsheet. I am trying to round data and sot some fields........I have formulas but could use help with tweaking it.

THX
 

Attachments

  • Book1.xlsx
    15.7 KB · Views: 44
You are quite close with getting the detail you need, once you have the "recipe list" (in cols A-C) for the customer of interest. Achieving your 3 objectives is going to be challenging unless you can compromise on the way your data is arranged. I would suggest that you set up a separate dataset for all your customer data showing all their recipe items. This can be in the same sheet as your doing your calculations, or a different one. The headers for this table might be:

Customer ID, Ingredient Name, Qty (lbs), Conv Factor, Conv Qty, lbs/grms ?

Each of these would be a separate column and all of the customer data would be listed, preferrably without blank lines. The details for a given customer ID would be "called" into your work area (A,B,C) from the table, using (eg) an array INDEX formula and the calculations you have defined would proceed from there. You will need to decide when and how you save the data, as the (A,B,C) work area would be overwritten by the next Customer ID entered.
As your wanting to format lbs and grams differently, I would suggest using two separate columns (to the right).

Hope those ideas may help get you started :)
 
Last edited:
Thx buddy. I am willing to re arrange data. Can your do a spreadsheet with your suggestion?

I would appreciate it
 
Im happy to help further, as long as you have defined the problem fully. Thats because I think I can provide a solution in a relatively short time. If however it becomes "the thin end of a wedge", I may have to leave it with you.
Give me a little time, and I will work on your attachment aiming to deliver your three objectives.
 
Hercules1946 said:
Im happy to help further, as long as you have defined the problem fully. Thats because I think I can provide a solution in a relatively short time. If however it becomes "the thin end of a wedge", I may have to leave it with you.
Give me a little time, and I will work on your attachment aiming to deliver your three objectives.
Click to expand...

Thx i appreciate it!!
 
Take a look at the attachment. For the moment, just play with this data to see how it works, and then set up your CusData and Workings on this model. To demo, you just need to select one of the Customer IDs from the drop down list in C1. Let me know what you think. When you have your data organised, I can then take you through how to import the formulae onto it.
If there is anything you want to change, please explain clearly, as it might mean modifying the formulae. Ive gone with the option of having a separate sheet for the Customer database, but if you like it could all be on the one sheet.
 

Attachments

  • Book1-2.xlsx
    27.2 KB · Views: 10
Hercules1946 said:
Take a look at the attachment. For the moment, just play with this data to see how it works, and then set up your CusData and Workings on this model. To demo, you just need to select one of the Customer IDs from the drop down list in C1. Let me know what you think. When you have your data organised, I can then take you through how to import the formulae onto it.
If there is anything you want to change, please explain clearly, as it might mean modifying the formulae. Ive gone with the option of having a separate sheet for the Customer database, but if you like it could all be on the one sheet.
Click to expand...

Will take a look and give you feedback in a few days.
 
Ok everything is working great.....thanks

on cell J2 on worksheet "workings" can we limit it to 2 decimal places for grams?

thx again
 
Omer1234 said:
on cell J2 on worksheet "workings" can we limit it to 2 decimal places for grams?
Click to expand...

On this amended attachment, the formula in Col J caters for grams to two decimals and Lbs to one decimal. Mine starts in J4 as previous. If you need it to start in J2, then copy up from J4 to J2:J3.
 

Attachments

  • Book1-2.xlsx
    27.3 KB · Views: 12
Hercules1946 said:
On this amended attachment, the formula in Col J caters for grams to two decimals and Lbs to one decimal. Mine starts in J4 as previous. If you need it to start in J2, then copy up from J4 to J2:J3.
Click to expand...

Thx for your help
 
Hercules,

If you wanted to scale components up / down based on different size of receipe .....what cell would you change?

Would it be when it calculates final receipe?.... Making 100 kilo vs 343 kilo would change components

thx
 
Omer1234 said:
making 100 kilo vs 343 kilo would change components

thx
Click to expand...

You can make any changes you wish to the recipes in CusData, and these will be carried across to Workings. You can also add new customers and recipes (down to row 50 at present).
When you say making a batch of 100 kilos vs. 343 would change components, why not just have your customer database show the recipe mixture for (e.g.) 1 kilo (all customers)?
You can then introduce a multiplier to calculate the total component volumes for any batch size (on the workings sheet).
Then you would only need to change your customer database if the recipe proportions were changed.
 
Hercules1946 said:
You can make any changes you wish to the recipes in CusData, and these will be carried across to Workings. You can also add new customers and recipes (down to row 50 at present).
When you say making a batch of 100 kilos vs. 343 would change components, why not just have your customer database show the recipe mixture for (e.g.) 1 kilo (all customers)?
You can then introduce a multiplier to calculate the total component volumes for any batch size (on the workings sheet).
Then you would only need to change your customer database if the recipe proportions were changed.
Click to expand...


Yes, I like your idea of "You can then introduce a multiplier to calculate the total component volumes for any batch size (on the workings sheet)."


let's leave receipe @ 1 kg, then I can enter new size and let excel calculate multiplier and scale up components accordingly

so how and what cell For the multiplier..... Can you repost spreadsheet please with multiplier sample
 
You can use any spare cell in the Workings sheet to enter the total batch size that your making.
Do you think it might be better if you did the changes and reposted the sheet? Otherwise my ideas/methods may be taking you away from the desired end result, and prolonging the process.
Also its important that you master the techniques involved so that you can drive forward the project yourself.

The first step would be to reconstruct the recipe proportions for each customer in Cusdata tab onto a percentage basis rather than differing batch sizes such as 343 kilos. Why dont you start with that and repost the sheet.
 
I have been traveling but will post by this weekend
 
Hercules1946 said:
OK - Look forward to hearing from you :)
Click to expand...

OK take a loo , I just did a simple multiplication :),I am hoping you will critique it with a better alterative ;-)
 

Attachments

  • Sort & Print.xlsx
    18.2 KB · Views: 11
Omer1234 said:
I am hoping you will critique it with a better alterative ;-)
Click to expand...
As I have little knowledge of your objectives in terms of functionality of the data, I do not feel comfortable criticising your efforts, or feel able to suggest alternatives.
If there is something thats not doing what you want, I will try to resolve that if you will explain the requirement.
By the way I did suggest the alternative of using percentages in Cusdata, and I noticed you haven't adopted that. Was it unsuitable for some reason?


Omer1234 said:
Notice - this should be 1 lbs and 99.79 grms
Click to expand...

In your original attachment you said that weights < = 1.5 lbs should give you grams so on that basis 553.38 grams would be correct as its only 1.22 lbs. Was this wrong, or have you changed the rules?
If your wanting to report in mixed grams and lbs this might need a different solution along the lines in my attachment, as otherwise using text gets cumbersome as you get more complex
 

Attachments

  • Sort & Print1.xlsx
    17.7 KB · Views: 11
Last edited:
You must log in or register to reply here.
Back
Top