# Spreadsheet to Calculate Dilutions from a Ratio



## Specus (Jan 11, 2013)

***PLEASE SEE PAGE 3 FOR AMENDED SPREADSHEET - ALL WILL BECOME CLEAR IF YOU READ THE COMPLETE THREAD***

Being a total :newbie: here, I have noticed lots of people referring to dilution strengths when referring to using APC, etc (i.e 1:10, 1:4).

I was thinking it might be helpful if I designed a quick Excel spreadsheet so someone can easily work out in millilitres how much product and how much water they require, to fill a certain sized bottle, or to obtain a final volume of solution.

I have attached a spreadsheet I (quickly) designed in Excel format (which is zipped as an attachment). Input your figures into the yellow cells, and the green cells should give you the correct amounts to make up your solutions.

I am assuming (maybe incorrectly, although I hope not) that when referring to 1:10 on an APC that means 1 part APC to every 10 parts water, so the spreadsheet has been designed with this in mind.

I have also included a litre to millilitre conversion in case anybody out there may work in imperial measurements 

So for example, if you have a 960ml sprayer bottle, and the ratio is 1:10, input 1 in cell B8 being the ratio of product (labelled "Input Dilution Ratio of Product") and 10 into cell D8 being the amount of water (labelled "Input Dilution Ratio of Water"), and then input 960 into the cell at B10 labelled "Input Amount of Final Solution Required (ml)" and the results will be displayed below in Green in millilitres ("Amount of Product 96" "Amount of Water 864").

Hope this helps people out there...if anyone spots any mistakes please shout and I can amend. Someone may have already done something similar, if so, I apologise.


----------



## Rayner (Aug 16, 2012)

Can't open file on android so haven't seen it but well done!

Good idea there mate, plenty of people would use that :thumb:


----------



## ottostein (Sep 11, 2012)

Cant open in excell  bummer

Excellent idea though


----------



## Turkleton (Apr 18, 2010)

Great idea and i'm sure a few people will find it helpful, but it's not rocket science doing 1 part to 10 parts is it?


----------



## Specus (Jan 11, 2013)

Turkleton said:


> Great idea and i'm sure a few people will find it helpful, but it's not rocket science doing 1 part to 10 parts is it?


No, very true, but 1:4 with an odd numbered final volume is a little more tricky?


----------



## Specus (Jan 11, 2013)

ottostein said:


> Cant open in excell  bummer
> 
> Excellent idea though


Really? I designed it in Google Docs and saved it as an .xlxs document and checked it opened at work in Excel...

Do you get an error message?

...and thanks...


----------



## ottostein (Sep 11, 2012)

No error message, just keeps asking to save the file everytime i try to open it, Cant find it in excell files either?


----------



## puckacostello (Apr 1, 2010)

Opened for me on Android with polaris office, good job!  

Sent from my HTC One X using Tapatalk 2


----------



## marc147 (Nov 21, 2011)

Very useful  thanks


----------



## Focusaddict (May 31, 2010)

Specus said:


> Really? I designed it in Google Docs and saved it as an .xlxs document and checked it opened at work in Excel...
> 
> Do you get an error message?
> 
> ...and thanks...


Opens up no problem in excel 2010.:thumb:


----------



## Bero (Mar 9, 2008)

I think your sheet is wrong :doublesho

10:1 should be 10 parts water to one part product. Your sheet calculates 9:1, or 3:1 if you try calculate 4:1. While nothing too bad with that if you use something 1:1 you sheet says 100% product and no water.

Cell B13 should be B10/(D8+1) :thumb:


----------



## Reflectology (Jul 29, 2009)

Bero said:


> I think your sheet is wrong :doublesho
> 
> *10:1 should be 10 parts water to one part product*. Your sheet calculates 9:1, or 3:1 if you try calculate 4:1. While nothing too bad with that if you use something 1:1 you sheet says 100% product and no water.
> 
> Cell B13 should be B10/(D8+1) :thumb:


but this wouldnt fit in a one litre bottle as you would have 1100ml


----------



## Bero (Mar 9, 2008)

Reflectology said:


> but this wouldnt fit in a one litre bottle as you would have 1100ml


It's a ratio....not a volume so, sorry but you're speaking rubbish!

90ml cleaner and 910ml water....= 1litre = 10-1 :wave::thumb:


----------



## Reflectology (Jul 29, 2009)

i know its a ratio. I can read but at the end of the day the guy did this off his own back so give him a bit of credit for having a go, i know how to work my ratios out he was only trying to help.


----------



## Specus (Jan 11, 2013)

Bero said:


> I think your sheet is wrong :doublesho
> 
> 10:1 should be 10 parts water to one part product. Your sheet calculates 9:1, or 3:1 if you try calculate 4:1. While nothing too bad with that if you use something 1:1 you sheet says 100% product and no water.
> 
> Cell B13 should be B10/(D8+1) :thumb:


In my original post I did say I assumed the ratio was expressed with product first and water second (i.e 1:10 would be one part APC and 10 parts water)..I wasn't sure if this was correct but it seemed logical.

I guess anyone is welcome to use it and alter it as they wish, the framework is hopefully there, but use it at your own risk, bearing in mind the calculation assumes the ratio is expressed with product first followed by water.


----------



## Bero (Mar 9, 2008)

Reflectology said:


> i know its a ratio. I can read but at the end of the day the guy did this off his own back so give him a bit of credit for having a go, i know how to work my ratios out he was only trying to help.


I wasn't knocking, just pointing out the error.



Specus said:


> In my original post I did say I assumed the ratio was expressed with product first and water second (i.e 1:10 would be one part APC and 10 parts water)..I wasn't sure if this was correct but it seemed logical.
> 
> I guess anyone is welcome to use it and alter it as they wish, the framework is hopefully there, but use it at your own risk, bearing in mind the calculation assumes the ratio is expressed with product first followed by water.


I was just trying to be helpful.

if you ask your sheet to calculate 1:10 it calculates 1:9. If you ask 1:4 it calculates 1:3 if you ask 1:1 it calculates 1:0. It's a simple fix though. :thumb:


----------



## Specus (Jan 11, 2013)

Bero said:


> I wasn't knocking, just pointing out the error.
> 
> I was just trying to be helpful.
> 
> if you ask your sheet to calculate 1:10 it calculates 1:9. If you ask 1:4 it calculates 1:3 if you ask 1:1 it calculates 1:0. It's a simple fix though. :thumb:


Ok I'll take a look later..at work at the moment. Thanks for the input.


----------



## Specus (Jan 11, 2013)

Bero said:


> I wasn't knocking, just pointing out the error.
> 
> I was just trying to be helpful.
> 
> if you ask your sheet to calculate 1:10 it calculates 1:9. If you ask 1:4 it calculates 1:3 if you ask 1:1 it calculates 1:0. It's a simple fix though. :thumb:


Just had a very quick look at the sheet...I used 1:10 on 10000 millilitres total volume which should be 1000 millilitres product to 9000 millilitres water mixture...is that right? If so, then the sheet is correct as this is what it comes out as if you input those numbers? It cant be 1000 millilitres product and 10000 millilitres water as that makes 11,000 millilitres? Apologies if I have got it all wrong...


----------



## Turkleton (Apr 18, 2010)

Specus said:


> Just had a very quick look at the sheet...I used 1:10 on 10000 millilitres total volume which should be 1000 millilitres product to 9000 millilitres water mixture...is that right? If so, then the sheet is correct as this is what it comes out as if you input those numbers? It cant be 1000 millilitres product and 10000 millilitres water as that makes 11,000 millilitres? Apologies if I have got it all wrong...


1:10 makes 11 parts, 1 part is any volume whatsoever, as long as the 10 parts is 10x that volume

1000ml:9000ml is 1:9, making 10 parts


----------



## Specus (Jan 11, 2013)

Ok, so I think the sheet seems to calculate the dilution as a percentage then, rather than as a proper dilution?

So 1:10 in my sheet gives you 1,000 millilitres to 9,000 millilitres water making 10% solution....mmmm...this is way more complicated than I first though...

Sorry folks...I should probably stick to what I am good at and stop dabbling in maths!


----------



## Bero (Mar 9, 2008)

Specus said:


> Just had a very quick look at the sheet...I used 1:10 on 10000 millilitres total volume which should be 1000 millilitres product to 9000 millilitres water mixture...is that right? If so, then the sheet is correct as this is what it comes out as if you input those numbers? It cant be 1000 millilitres product and 10000 millilitres water as that makes 11,000 millilitres? Apologies if I have got it all wrong...





Bero said:


> Cell B13 should be B10/(D8*+1*) :thumb:


:thumb:


----------



## Specus (Jan 11, 2013)

Bero said:


> :thumb:


Spreadsheet updated...please use the one attached here...thanks to Bero for his expert advice - think it should be accurate now.

Apologies for the mis-understanding with original spreadsheet!


----------



## Specus (Jan 11, 2013)

ottostein said:


> No error message, just keeps asking to save the file everytime i try to open it, Cant find it in excell files either?


Have you tried saving it and then finding and opening it? What happens then?


----------



## Reflectology (Jul 29, 2009)

Bero said:


> I wasn't knocking, just pointing out the error.
> 
> I was just trying to be helpful.
> 
> if you ask your sheet to calculate 1:10 it calculates 1:9. If you ask 1:4 it calculates 1:3 if you ask 1:1 it calculates 1:0. It's a simple fix though. :thumb:


Thats a fair comment mate.

Indeed ratios can be complicated at times especially when you try to calculate the ratio of foam through a lance. Now theres one for the IQ busters.


----------



## stantheman (Dec 23, 2010)

Reflectology said:


> Thats a fair comment mate.
> 
> Indeed ratios can be complicated at times *especially when you try to calculate the ratio of foam through a lance. Now theres one for the IQ busters*.


Not a problem for our James_Death he's got it off to a 'T' :thumb:


----------



## TopSport+ (Jul 22, 2012)

excellent idea


----------



## dooka (Aug 1, 2006)

Reflectology said:


> Thats a fair comment mate.
> 
> Indeed ratios can be complicated at times especially when you try to calculate the ratio of foam through a lance. Now theres one for the IQ busters.


If I remember correctly, with my lance and pressure washers' current set up, it takes 16 litres to empty a 1 litre bottle..


----------



## CraigQQ (Jan 20, 2011)

Reflectology said:


> Thats a fair comment mate.
> 
> Indeed ratios can be complicated at times especially when you try to calculate the ratio of foam through a lance. Now theres one for the IQ busters.


that's actually not too hard to work out..

put your snow foam mix in a 1L bottle, and measure how long it takes for your pressure washer to completely empty the 1L bottle.
calculate the amount of minutes taken by the flow rate on your machine

example(I've never tried this it's purely a guess)

I have 1L of snowfoam mix, say 10:1 in the bottle. (so 90ml of foam to 910ml of water)
the it takes the pressure washer 7 minutes to empty the foam bottle while the water flows through.
my Kranzle flow rate is 10L per minute, so that 1L of snowfoam mix has had 70L added in 7 minutes while the water mixed with it and sprayed out.

that means that 90ml of product, has been mixed with 70910ml of water.

so if the 90ml is taken as 1 part, then 90 goes into 70910 
788 times.. so the snowfoam mix at the lance should in theory be 788:1 (water: product)

(788 is rounded to the nearest whole number.. actual number is 787.8888888888889)

If this is wrong feel free to correct it someone. Math is not my strongest point.


----------



## CraigQQ (Jan 20, 2011)

dooka said:


> If I remember correctly, with my lance and pressure washers' current set up, it takes 16 litres to empty a 1 litre bottle..


Cheers for the actual number Rob..

if my above calculations is right then that means

16910, assuming your putting 10:1 in the lance

your mix at the lance end should be 188:1 (that seems rather low tbh.)


----------



## PrestigeChris (Nov 17, 2009)

if you have an iphone there is an app called mix rite

its designed to work out ratios for 2 stroke engines but still does the same job


----------



## Nicholas (Jan 9, 2012)

Good idea, opens ok and works on IPad thank you


----------



## Bero (Mar 9, 2008)

CraigQQ said:


> that's actually not too hard to work out..
> 
> put your snow foam mix in a 1L bottle, and measure how long it takes for your pressure washer to completely empty the 1L bottle.
> calculate the amount of minutes taken by the flow rate on your machine
> ...


But flow rates are 'maximum' and will certainly be considerably less when you place a gauze restriction, siphon tube and nozzle in the flow path. The only way to do it accurately is fill your litre bottle with water, set the dial to one extreme and spray the PW into a container until the SF bottle is empty them measure how much water is in the container and do the calculation. I did that one day with my karcher as Bilt Hamber told a few people 4% panel impact ratio was what you needed. I've never bothered with the Kranzle., just twiddle the knob until it looks about right.


----------

