This article offers a mind-map based anatomy (see image below) of the Dynamic Ration Computation Table that is at the heart of the functionality in my popular Excel-VB Driven Ration Formulator Software.
My purpose here is to offer potentially useful practical insights into the thinking behind the construction of that table, for best practice purposes.
This would benefit anyone interested in better understanding how this app works, as well as those interested in learning how to build time, effort and cost-saving worksheet data entry and report generation tables.
Members of my Excel Heaven Visual Basic Automation Club will get FREE copies of the step-by-step screenshot tutorial video, in which I explain how this dynamic table was built from scratch.
You can watch a step-by-step screenshot demonstration tutorial of this app in use at www.tinyurl.com/RealRationDemo
Below are explanations of the functions of the formulas in each of the key columns in the ration computation table.
In the screenshot demonstration video tutorial that will be sent to member of my Excel Heaven Visual Basic Automation club, I explain how EACH formula is constructed, with regard to syntax etc. Click here to request a copy.
1. Price/Unit (Kg)
=IF(D3=””,””,IF(D3=0,0,INDEX(feedIngredientsDbase,MATCH(D3,nfFeedIngredients,0),10)))
When you choose an “Ingredient Name” from the drop menu in a cell in column “D”, the above formula retrieves the matching Price/Unit for the item, from the “Settings” worksheet (which holds the Nutrients Composition for ALL the ingredients available for use along with their prices on a row by row basis)
2. Pr% in ration
=IF(D3=””,0,IF(D3=0,0,(INDEX(feedIngredientsDbase,MATCH(D3,nfFeedIngredients,0),2)*$E3)/100))
When you you choose an “Ingredient Name” from the drop menu in a cell in column “D”, the above formula retrieves the matching PERCENT %PROTEIN VALUE for the Item, from the “Settings” worksheet
3. Amount (Kg) Std – 50Kg
=IF($H$26=””,(E3/100)*50,(E3/100)*$H$26)
This formula uses the “% in Ration” value in each ingredient row/cell to derive the physical “Amount(Kg)” of THAT ingredient that will make up part of the “Target Feed Size (kg)” i.e. total kg feed you want to mill. Same formula adjusted Works for “Calcium in Ration” and “Fibre% in Ration” columns
4. kcal ME/ in ration
=IF(D3=””,””,IF(D3=0,0,(E3/100)*INDEX(feedIngredientsDbase,MATCH(D3,nfFeedIngredients,0),5)))
This formula uses the “% in Ration” value you type in each ingredient row/cell to derive the equivalent ENERGY contribution (kcal ME/g) from THAT ingredient to the total amount of feed to be milled.
This Ration Computation Table uses the above highlighted formulas to create a dynamic effect that enables the user focus on posting his/her preferred “% in Ration” values for ANY combination of feed ingredients s/he wishes to use in deriving a specific ration formula.
The table returns instant ration formulas, complete with prices with every change, until s/he arrives at one that meets his/her needs.
This approach makes it easy for the user of this Ration Computation table to quickly TEST different combinations of ingredients, based on their respective prices, as well as their nutrient content (e.g. protein , energy, calcium and fibre).
Without this method the user would have to REPEATEDLY type in the respective values for each of those variables anytime the “Ingredient Name changed on a specific row.
Major time/effort savings would be lost. And chances of avoidable user data entry errors greatly increased!
Members of my Excel Heaven Visual Basic Automation Club will get FREE copies of the step-by-step screenshot tutorial video, in which I explain how this dynamic table was built from scratch.
Click here to request a copy of the video tutorial.
Visit www.excelheaven.biz to learn more about the club.
