Journeyman Posted August 13, 2021 Share Posted August 13, 2021 (edited) @Coopers DIY Beer Team(or anyone else who knows) I have been gifted 2 Mr Beer cans, the Long Play IPA and Diablo IPA. I am trying to plan beers to make with these but for that I use IanH's spreadsheet - unfortunately there is no data in the Kit section for any Mr Beer brews. Where can I find EBC, IBU, info for at least these 2 or better, the Mr Beer range so I can update the spreadsheet. Just the total for the can EBC & IBU would be fine - I can use a formula to calc the figures for brew size. @Coopers DIY Beer Team- On the basis that I seem to recall a comment about Mr Beer having something to do with the USA I tried there - also no joy. But you might want to drop them a line that their security cert is out of date by 4 days... Edited August 13, 2021 by Journeyman Link to comment Share on other sites More sharing options...
Journeyman Posted August 13, 2021 Author Share Posted August 13, 2021 I think I've found what I need - not sure why I didn't check the actual can info on the Aussie site. But I'm going to update them all into the spreadsheet. Link to comment Share on other sites More sharing options...
Coopers DIY Beer Team Posted August 13, 2021 Share Posted August 13, 2021 10 minutes ago, Journeyman said: I think I've found what I need - not sure why I didn't check the actual can info on the Aussie site. But I'm going to update them all into the spreadsheet. Cool. Have a great weekend! Link to comment Share on other sites More sharing options...
Journeyman Posted August 13, 2021 Author Share Posted August 13, 2021 7 minutes ago, Coopers DIY Beer Team said: Cool. Have a great weekend! Yeah... spoke too soon. That doesn't work. The can EBC & IBU need a formula to adjust for the SS to show them e.g. for Coopers OS it's the can value x 1.25 /23. Changing the 23 to 8.5 or 10 doesn't work. le sigh At least I now have an idea of what the cans are - I can compare to similar large volume cans and see how I go. Link to comment Share on other sites More sharing options...
Thirsty Jim Posted September 6, 2021 Share Posted September 6, 2021 Scratch my previous post (hidden now). The formula works for the IBU and EBC in terms of brew volumes but it throws out OG, FG and Alc% outputs. Houston's working on it. 4 Link to comment Share on other sites More sharing options...
Thirsty Jim Posted September 7, 2021 Share Posted September 7, 2021 (edited) (Talking to myself again...) What are we trying to do ? We trying to establish a formula that will make Ian's SS work with Mr.Beer kits. Why don't they work with Ian's SS ? Because if you enter their data in the Kit tab and copy the existing formula down it calculates EBC and IBU as Can Value (variable) x Can Volume (1.7 litres) /Brew Volume (23 litres) and we know the Mr Beer cans are only 1.3 litres and brewed to 8.5 litres But why does that matter ? EBC and IBU are qualitative not quantitative measures. It doesn't matter how big the can is its EBC and IBU values will be the same. True but the values are affected by how much water we add, i.e. the volume of the brew . So the formula for Coopers cans has to be adjusted for Mr.Beer cans to give an accurate result for an 8.5 litre brew. So we change the formula from Can Value x 1.7kg / 23 litres to Can Value x 1.3kg / 8.5 litres ! Doesn't work. If you do that the values on the Main page go through the roof when you set the brew volume to 8.5 litres. That is the whole problem. We need a formula that scales the EBC and IBU values down to 8.5 litres without inflating them. OK then. Let's do a comparison. Coopers Draught and Mr Beers Long Play IPA have identical EBC and IBU can values - 130 and 420. Correct. In 23 litres the Coopers Draught returns a Brew value of 9.6 (EBC) and 22.8 (IBU) Correct. So what we want to do is have the SS return those brew values for a Mr Beer Long Play IPA in 8.5 litres Umm... I think so. In other words what we want is for the SS to return the same brew values for the Mr Beer kit in 8.5 litres as the Coopers kit in 23 litres OK So we use the formula =Can Value*1.7/(23*(23/8.5)) But how... No buts. Suck it and see. Plug the formula in under the Kits tab and them return to the Main page, select Long Play IPA OK... here goes ... Nope no good... It returns 11.6 IBU and 3.6 EBC That's for 23 litres dopey. Change the brew volume to 8.5 litres.... Oh yeah...Son of a gun. It returns IBU 31.0 and EBC 9.6 - and is identical to a Coopers Draught brewed at 23 litres. There you go But it still says the weight of the kit is 1.7kg Then change it to 1.3kg OK... nothing happened ! Not in F6 and G6 but the OG and FG and the ALC% all changed Ah. So there is the problem. Nope. Ofcourse they change because the weight of the kit changed the gravity of the solution, and ALC% is only the difference between OG and FG So why didn't they change the values of IBU and EBC in F6 and G6 ? Firstly because Ian made those 'look up' cells. They draw their values from the kit sheet, multiply them by 23 and divide them by C3 (brew volume i.e 8.5 litres) Nothing in that formula about the weight of the kit. In fact you enter any value other than zero in C6 and it will always return the same values in F6 and G6. Well bugger me. No thanks. Have a look at the rest of the main page and set the beer style to any of the Pale Ales. You will find you are making beer at 5.0%ABV from an OG of 1.047 which is in the ball park of most Pale ALe styles. EBC is a tad low for English and American styles but bang on for an Australian. Add in 250g of Liquid Malt and you lift it into IPA territory. OK, I am convinced. So what do I need to do. Enter the Mr. Beer data on the Kits sheet but instead of copying the formula down use the =Can Value*1.7/(23*(23/8.5)) formula instead . Put you can values for EBC and IBU into collumns E and F and you are done Cool. Good work captain. You deserve a beer. Don't mind if I do...cheers ! Edited September 7, 2021 by Thirsty Jim correction 3 1 Link to comment Share on other sites More sharing options...
iBooz2 Posted September 7, 2021 Share Posted September 7, 2021 @Thirsty Jim excellent work yet again James and much appreciated. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now