Mr Beer Data

[Journeyman]

@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

[Journeyman]

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.

[Coopers DIY Beer Team]

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! 

 

 

 

[Journeyman]

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.

[Thirsty Jim]

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.

[Thirsty Jim]

  (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

[iBooz2]

@Thirsty Jim excellent work yet again James and much appreciated.