No Sparge Calculation Trouble

So I'm attempting to do a few small 2-gallon all-grain batches. I've been doing PM 5 gallon batches for awhile now, but I don't have the equipment to go all-grain with high volume batches, so I want to do smaller AG batches on my stove top. To do so, I want to use the No Sparge method. I have How to Brew at home and have been researching the method extensively online.

Here is an explanation of the calculation I've been using: Skip the Sparge

I set up an excel sheet to do the calculations for me. From these calculations I input the following:

1. Calculated OG that I got from ProMash after entering the ingredients and scaling it to a 2 gallon batch, 1.078 (but enter it as just 78 per the instructions)
2. Total Grain Bill (5.7 pounds)
3. Recipe Volume (2 gallons)
4. Boil Volume (2.5 gallons, to account for evaporation during the boil)

From the calculation I need to get the following outputs:

1. Scale-up factor for the grain (so I calculate the new grain bill)
2. Volume of water to add for the mash
3. Volume of water to add during mash-out
(there are other outputs of the calculations but these are the three I need help with)

So when I input a recipe that I've calculated and scaled that has a relatively low OG, the results of the calculation seem to work out fine. However, when I try to enter the numbers as I've stated above (a high OG beer), I get a negative number for the volume of water to add during mash-out and I can't figure out why. Can someone help me with this, or does anyone have more experience with No Sparging than I?

Here is a link to the Excel file I'm using: NoSpargeCalculator

Can someone help me with these calculations? Thanks.

Can anyone help?

I'm not sure I understand what's going on here. So, the recipe you are wanting to make has 5.7 lb grain, and you want to end up with 2.5 gallons before the boil?

Let's assume 1.5 qt/lb of mash water. This means 1.5*5.7 = 8.55 qt = 2.14 gal of mash water. Your grain will retain 0.1 gal of water for each pound of grain. So, you will end up with 2.14 - 0.1*5.7 = 1.57 gal of water available. Now, this means you should add 2.5 - 1.57 = 0.93 gal of water for your "mash out" (which is really a sparge, despite it being called otherwise).

So...
Mash in: 2.14 gal
Mash out: 0.93 gal
Total collected wort: 2.5 gal

I'm really confused why the spreadsheet is calculating a new grain bill.

It's calculating a new grain bill because I'm not doing a regular sparge, so to make up for the loss of efficiency from not sparging, you have to up the grain bill to provide more starch for conversion.

cmdrico7812 said:

It's calculating a new grain bill because I'm not doing a regular sparge, so to make up for the loss of efficiency from not sparging, you have to up the grain bill to provide more starch for conversion.

Click to expand...

In that case, it seems obvious what's going on...the grain bill is large. It says in the spreadsheet 2 qt/lb of mash water? 7.97 lb * 2 qt/lb = 15.94 qt = 3.99 gal. Then your grain will absorb some, leaving you with 3.19 gal of water available. So, you would add about -0.7 gal of mash out water to get you to 2.5 gal. Your mash ratio of 2 qt/lb is just too large for this amount of grain and boil size.

I went with the default water/grain ratio that was given on the web page that explains the calculations. I changed it to 1.5 so now I'll rerun my numbers and see what I get. Thanks.