Hello all,
I start with a pic so its easier to explane.
Capture.JPG
How can this be made so into one table that its possible input percentages or weight in grams from the total?
Now I'm using two different tables, other which calculates percentage from total, and other which calculates ingredient weight.
Edit: I added the original sheet
Last edited by naubusan; 08-07-2017 at 07:42 AM. Reason: Added excelsheet
Try this in C9 copied down:
=B9/100*B$6
and the other way in B9 copied down:
=C9/B$6*100
Last edited by AliGW; 08-07-2017 at 07:42 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
or using a setup like the one in the sheet...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
You guys are fastOriginally Posted by AliGW
Thanks
This is what I tried to achieve but this formula gives an error.
Capture2.JPG
I edited First post to include sheet which calculates weight in grams from the total.
You can't have both on the same table - you said you had two tables - use the first in the percentage to grams table and the second in the grams to percentgae table. Adapt the cell references for the second table if it is in a different place.
Tried to google this and found some vba scripts which were working like this. But I don't understand vb enough to create working script on my purpose.
Ah, OK - you want it in one table. Sorry - my misunderstanding.
Did you look at post #3?
Yes, Thanks Glenn, that's working solution although quite not what I was thinking.
Hi all- This seems to work. Paste this in B9 and copy down:
=IF(ISFORMULA(C9),"-",C9/$B$6*100)
...and this in C9 and copy down:
=IF(ISFORMULA(B9),"-",$B$6*B9/100)
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
Last edited by leelnich; 08-07-2017 at 08:54 AM.
Gives me theres problem with this formula error. And I don't think this this is going to work as I want all data in one table?Hi all- This seems to work. Paste this in B9 and copy down:
=IF(ISFORMULA(C9),"-",C9/$B$6*100)
...and this in C9 and copy down:
=IF(ISFORMULA(B9),"-",$B$6*B9/100)
Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
So if it isn't what you want... what DO you want? You need to explain. There aren't many mind readers here...
One table. Typed percentage of total weight changes values in the weight in grams column and vice versa, typed grams changes percentage.
Your solution is good, but have to use drop down list.
This is exactly how my formulae behaved. Both cells are formulas, so initially both yield "-". When you type something in either cell and press enter, it no longer contains a Formula, so the other cell calculates appropriately. Barring VBA, this is (I think) the only way to achieve your goal.
Wait, is B6 amount FIXED, or is it summing all the weights in column C, meaning the WHOLE percentages column must change whenever it does? The latter would explain why you're getting a circular reference problem.
Last edited by leelnich; 08-07-2017 at 09:51 AM.
I had to change commas to semicolons to get that working, different excel versions I think.
Yes this way it works but overwrites the formula.
No, not different Excel versions, but different locales: where the UK, US and Australasia use the comma, other parts of the world use semi-colons. That's a broad brush I've used, but essentially where a comma is used as a decimal separator, semi-colons are used as separators in formulae. Where a decimal point is used, there a comma is used in formulae. That's why we ask you for your location: unfortunately, your profile entry is not very helpful.
Thanks @AliGW, problem explained!The overwrite is unavoidable, yes? If you wish to switch an "entered" cell back to formula, you'll have to copy it from another cell. Of course, you could use VBA's Worksheet_Change event to respond to entries in either column by calculating the corresponding values, eliminating the overwrite problem.
Last edited by leelnich; 08-07-2017 at 10:21 AM.
Yes that is what I try to achieve.
This should work. It's already part of the attached workbook.NOTES
Please Login or Register to view this content.
This code is worksheet-specific. To use it with another workbook/worksheet:
1) Open the relevant workbook.
2) Right-click on the relevant worksheet tab and select View Code.
3) Paste the code from above in the Worksheet Module that appears.
4) Close the Visual Basic Editor window. This returns you to Excel.
That's it! Now save the workbook as a Macro-Enabled (.xlsm) file:
1) Select Save As from the File tab on the command ribbon.
2) Choose a folder
3) Click in the Save as type: box
4) Select Excel Macro-Enabled Workbook
5) Click Save
Note that this is a BRAND NEW FILE. It has the same NAME as the old version, but a different FILE EXTENSION (.xlsm)
To avoid confusion, you should delete the old version, which has a .xlsx file extension.
Last edited by leelnich; 08-07-2017 at 11:59 AM.
Thank you leelnich very much! Just perfect!
Is this script possible to get working so, when B6 value is changed that other values updates automatically?
Yes, but first we need to examine the underlying logic of this entire approach.
1) In this context, Percentages should always add to 100. If one is changed, others must change to compensate.
2) Changing an individual weight should change Total Weight, unless other weights are adjusted to compensate.
3) Given the above, changing one individual weight or one percentage should cause recalculation of the ENTIRE TABLE.
None of the solutions - VBA or worksheet function - offered so far truly address these issues. I think you must define HOW these adjustments should be made in both cases.
Last edited by leelnich; 08-07-2017 at 01:57 PM.
Changing total weight should change whole table. Changing Ingredient percentage or weight only changes that row.
This is used to calculate spices to certain amount of food (total weight). Sometimes amount is given grams to certain amount of food and sometimes spices percentage per certain amount of food. Sorry, when not native english speaker, explaining this is difficult
The thread is marked as solved - is it???
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks