How the Quick Water Calculator Works

Discussion in 'Recipe Editor' started by AHarper, Nov 12, 2018.

  1. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    #1 AHarper, Nov 12, 2018
    Last edited: Nov 12, 2018
    I decided to reverse engineer the Quick Water Calculator just to find out how it worked out the values that always seemed to be different from those I plumbed into the Recipe Editor.

    I could never rationalise the Sparge Water requirements given my input values for Mash and Sparge values. So this is how I think it works but I'm willing to be proved wrong.

    This is what I discovered:

    As you may have already worked out the starting point is what you choose to set for the recipe TARGET and BATCH SIZE.

    The Estimated Boil Size is not so important at this point but will make a difference as we shall see later.

    The next important value that is to be considered is the Mash Thickness (as you would imagine) that, together with the Weight of the Fermentables, defines the amount of Strike Water required.

    There is some weird calculation in the background when the volume exceeds the Mash Tun limit (as set in the Profile) but I have yet to figure out what the formula is - it is NOT just Batch Size + Grain Absorbtion + MT Dead Space.

    There are six values set in your profile that are used in the calculations:
    A. Grain absorption losses
    B. Mash Lauter Tun dead space
    C. Boil off losses
    D. Kettle dead space
    E. Hops absobtion losses
    F. Misc. Losses

    Additional values used are:

    G. Batch Size
    H. Total Fermentable Weight (Grains Only - even if marked Late Addition)
    I. Starting Mash Thickness (in Mash Guidlines section)

    What is NOT used are the values you put in for Mash and Sparge entries in the Mash Guidelines.

    Some intermediate calculations are made using the values above.

    J. Strike Water = Fermentables x Starting Thickness (= H x I)
    K. First Runnings = Strike Water - (Grain Absorbtion + MT Dead Space) (= J - (A+B))

    If we work with some real values it might be easier to understand. (I hope!)
    Assume:
    A. Grain absorption losses 3.5
    B. Mash Lauter Tun dead space 1.3
    C. Boil off losses 3.7
    D. Kettle dead space 1.6
    E. Hops absobtion losses 0.6
    F. Misc. Losses 1
    G. Batch Size = 20 lt
    H. Fermentables = 3.5 kg
    I. Thickness = 3 liters/kg
    J. Strike Water 10.5
    K. First Runnings 5.7


    For the Target set to KETTLE

    Amount in Kettle = Batch Size + Boil off Losses (= G + C)
    = 20 + 3.7
    = 23.7
    Sparge Water Volume = Amount in Kettle - First Runnings (= G + C - K)
    = 23.7 - 5.7
    = 18 litres
    Total Water Required = Strike Water + Sparge Water
    = 10.5 + 18
    = 28.5 litres

    For the Target set to FERMENTOR

    Amount in Kettle = Batch Size + All Kettle Losses (= G + C + D + E + F)
    = 20 + 3.7 + 1.6 + 0.6 + 1
    = 26.9
    Sparge Water Volume = Amount in Kettle - First Runnings (= G + C + D + E+ F - K)
    = 26.9 - 5.7
    = 21.2 litres
    Total Water Required = Strike Water + Sparge Water
    = 10.5 + 21.2
    = 31.7 litres

    Deselecting the Late Additions check box will increase the Boil Gravity and therefore increase the calculated IBU values it does not affect the Mash and Sparge calculations.

    After getting the results from the calculations you can then put the Boil Size and Mash and Sparge volumes into the recipe - it should all make sense now.

    Adjusting the Boil Size to the the calculated Amount in Kettle will modify the Boil Gravity and therefore modify the calculated IBU values. If you don't do this step the recipe will not show the correct IBU value.

    I hope this is of some help to those who, like me, wondered how the quick calculator worked and the values never look like what was expected.
     
    avantassel and Trialben like this.
  2. jmcnamara

    jmcnamara Well-Known Member

    Joined:
    Aug 29, 2012
    Messages:
    2,457
    Likes Received:
    1,947
    Trophy Points:
    113
    Gender:
    Male
    Location:
    Rosedale, MD
    Rewrite this a little bit and I think this should go in an FAQ. Good job
     
  3. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Hi there, do you mean de-personalise it? Yes it can be done. I need to know from the developers if it is factually accurate though. There is still the "problem" of the test against the profile Mash Tub Volume. I can't resolve the test formula yet. More work to do. Thanks for response.
     
  4. jmcnamara

    jmcnamara Well-Known Member

    Joined:
    Aug 29, 2012
    Messages:
    2,457
    Likes Received:
    1,947
    Trophy Points:
    113
    Gender:
    Male
    Location:
    Rosedale, MD
    I don't know if de-personalize is the right word, but maybe more like a textbook than a brew day log? Maybe some screenshots would help if it does become an FAQ
     
  5. Trialben

    Trialben Well-Known Member

    Joined:
    Mar 30, 2016
    Messages:
    9,420
    Likes Received:
    9,469
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    Pest control tech
    Location:
    Palmwoods QLD
    Yep that awesome stuff!
     
    AHarper likes this.
  6. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    I hope this works but I made an easy to use spreadsheet that you can plug in values to reflect your brewing system losses etc. (as you should have in your profile) and have sliders to adjust mash details and batch size. It does the same as the Quick Water Calculator but is more flexible as you can quickly adjust values to match your desired outcomes.

    If this upload works then unzip the spreadsheet and give it a go. There are no macros in it so it should be safe.
    Essentially, change the values in the GREEN boxes in rows D,F,G,M,N,O and P (They are in your Equipment Profile). The value in row T is for your fermentor Trub losses.
    Use the sliders to adjust your Grain weight, Mash Thickness and Target Batch size to see your water requirements change like magic.

    Please let me know if you have any problems.
     

    Attached Files:

    avantassel likes this.
  7. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Updated Calculator Version 2
    =======================

    As per the entry of Tuesday @ 7:43 PM but with additional options to include a calculator for adjusting wort volume BEFORE fermentation so you can get the ABV % you want to package.

    In Row T of the spreadsheet is a slider to adjust the Post Boil Specific Gravity of the wort you now have in the FV.
    In Row W there is a slider to adjust the Post Ferment STOP Specific Gravity as defined in the recipe.

    The result of these adjustments will result in the display of the resultant ABV% at the end of fermentation. This is the same calculation as set out in the Calculators on this web site but is a more flexible view that displays the result of any changes in real time.

    In Row U there is a slider to adjust the amount of water you can add to the wort - if you want to tweak i.e. lower the ABV% and increase the volume of the packaged beer. This uses the same equation as in the Calculator also on this web site.

    Enter the Fermentor losses (TRUB) in the green box in Row Y and the Packaging Volume will be displayed in Row Z. This is not essential but is just a handy view.

    The whole spreadsheet fills a single A4 sheet so, when printed out, the figures can be included in your records.
     

    Attached Files:

  8. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    Nice work is this liters or gallons?, you may want to define that
     
  9. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Yes you are right. I should have made that more obvious.

    It is currently set up for working in Litres and Kilo grams - which is what I work in.

    I will see what effect there is when considering Gallons and Pounds. If there is an Issue I can add a check box option to select appropriately.

    It may take a while to work out as I will have to reset all my profile settings and test a recipe - on paper of course - I'm not brewing again for a while.
     
  10. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    I also added the ability for larger batches in the slider in mine, I brew 12 gallon finished myself, you may want to just add a drop down with both Liters/gallons and the cells feed off that field's conversion for gallons
     
  11. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Hi Ozarks MB,

    I took another look at the spreadsheet and did a few updates - now on Version 4 - that have the Metric / US selections and changes to layout and printing.

    If anyone else wants to try it out then there are a few instructions:

    1. The unzipped spreadsheet does have some code behind it now so you will need to have your Excel (>= V10) set up to allow Macros.
    2. On first load it will ask you to give a name for your recipe for print out purposes. The Name goes in the Left Header section and the whole A4 page for print out (see graphic). I adjust my zoom value to 75% so I can see the result of adjusting the sliders at same time.
    3. There are 8 cells that need to be filled in before the spreadsheet can be used. These are in Rows:
    D Mashtun Volume
    F Grain Absorbtion Losses
    G Mashtun Dead Space
    M Boil Off Losses
    N Kettle Dead Space
    O Hops Absorbtion Losses
    P Misc Losses and
    Y Fermentor Losses (Trub)

    These are the same values held in your Equipment Profile.
    These values are in Green background boxes if working in Metric and Orange if working in US Gallons. The Button at the top of the sheet switches between the two options. Once these values have been entered the calculator can be used.

    How to Use the Calculator
    The Slider below line A adjusts the weight of Grain fermentables
    The Slider below line B adjusts the Mash Thickness
    The Slider below line C adjusts the Batch Size.
    The Columns under TARGET shows the results for a KETTLE or FERMENTOR target. Both columns adjust at the same time.

    Tweaking the recipe values gives your resultant volumes in lines Q and S.

    The next section calculates for dilution of the fermented wort - if required.
    Adjust the slider below line T to the SG value of your boiled wort.
    Adjust the slider below line W to the target post ferment SG - this is the target final SG from the recipe.
    The resultant PRE FERMENT SG is displayed in line V. This will alter as you add water to the wort.
    The ABV% is calculated in line X.
    Adjusting the slider below line U will show you what happens to the PRE-FERMENT SG and the resultant ABV%.
    You can use these sliders to tailor the brew to your requirements.
    The final boxes in line Z shows the volume of beer (very approximately) that can be packaged.

    Playing about with the sliders can show you what happens if you change values to what you really get after the boil if it is different to the expected values from the recipe. Remember to manually set the post boil SG to what you actually achieve - IT IS NOT INTERACTIVE WITH CHANGES IN MASH VALUES.

    If you have any problems with the calculator please let me know.

    I HAVE NOT PROTECTED THE SHEET IN ANY WAY SO BE CAREFUL NOT TO ALTER ANYTHING OTHER THAN THE SLIDERS AND THE EQUIPMENT PROFILE BOXES LISTED ABOVE.

    upload_2018-11-20_1-48-8.png
     

    Attached Files:

  12. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    Hi so I said gallons but I was generalizing and messed up on that sorry, I really should have said imperial because our profile is listed in quarts not gallons sorry again
     
  13. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    but I figured it out thanks
     
  14. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Hi Ozarks, (Seems strange conversing with a mountain range.. :)

    Ok No problem. V5 will be Imperial. It should just be a simple change of multiplier.

    I'm sure you, nor anyone else, actually needs this spreadsheet anyway - I did it as an exercise to better understand how the Website calculator works and add in some extra calculators. I hope you don't thing I was trying to "teach Granny to suck eggs" as they say here - maybe they say that there too?

    This brewing lark is all consuming - time wise and beer wise..

    Alan
     
    Trialben and jmcnamara like this.
  15. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    yes it comes out the same for me with my amounts as the web site thanks
     
  16. Ozarks Mountain Brew

    Staff Member

    Joined:
    Nov 20, 2012
    Messages:
    7,767
    Likes Received:
    3,976
    Trophy Points:
    113
    Gender:
    Male
    Occupation:
    IT Managment
    Location:
    The Ozark Mountains of Missouri
    no need to change it, it's easy to figure out the quarts, mine uses both gallons, quarts and pounds, the only thing that's throwing me is the Q/P mash thickness, on this site I use "Starting Mash Thickness: 1.7 qt/lb" on your sheet I had to use 0.50 to get the same results
     
  17. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Yes that was a problem are here too... More work needed I'm sure... it will give me something to do lol
     
  18. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    Version 5 Updated to handle quarts better...
    Also highlighted Sparge water levels - just makes it easier to read
    This one has a macro in it too.
     

    Attached Files:

  19. AHarper

    AHarper Well-Known Member

    Joined:
    Mar 23, 2018
    Messages:
    400
    Likes Received:
    357
    Trophy Points:
    63
    Gender:
    Male
    Occupation:
    Retired
    Location:
    Sussex, UK
    An updated version with the ability to switch between Units as before (Metric / US), The ability to Name the recipe on the sheet which can also lead you to save to a separate archive spreadsheet where recipes have their own tab and a snapshot of the calculations. This allows soft copies of all the data. You also have the option to print out the page for documentation.
    This version has been written for Excel V10 though it may work in later versions. It is also based around A4 size paper but you can always adjust it to fit US Letter size.
    It is not protected so if you play about with it then it's at your own risk.
    The Spreadsheet HAS code in it - and it is clean of any mal-ware but your system needs to know it is a Macro enabled XLS file.

    On Loading:
    The un-named sheet opens and a message box opens asking for a Recipe Name. It won't let you leave without filling in a name.
    The default option is No Name Given.
    upload_2019-2-17_0-3-12.png upload_2019-2-16_23-39-29.png

    You have the option of switching between US (Gallons / Quarts / Pounds) and Metric (kilograms / liters).
    upload_2019-2-16_23-45-31.png upload_2019-2-16_23-45-56.png

    Pressing the [Name Recipe and Copy] button will ask you to provide a Recipe name (again... well it will be different)
    and in the background an archive file - called WaterCalcsStore.xlsx will be created - if it doesn't exist already - and a copy of the calculations is created in a new tab in the sheet. If the archive file already exists then the tab is added to the file. The system won't let you copy it if the name already exists.
    upload_2019-2-16_23-54-48.png
    After the copy function the system asks you if you want to print out a sheet. If all is successful then a message box informs you of the data copied.
    upload_2019-2-16_23-55-47.png The main sheet has the recipe name deleted and is saved.

    upload_2019-2-17_0-2-32.png

    Start again.... if you have lots to do.

    Have fun and let me know if you discover any major problems I haven't come across.
     

    Attached Files:

Share This Page

arrow_white