Ratio Computation

mark692

Active Member
Joined
Feb 27, 2015
Messages
270
Office Version
2016
Platform
Windows
Hello guys i have a small laundry business, i want to know the ratio between the numbers of wash and the number of detergent used per day, i have i table like this

ABCD
1DateWashDetergentRatio
2August, 01 201864
3August, 02 201853
4August, 03 201842
5August, 04 2018
6August, 05 2018
7August, 06 2018
8August, 07 2018
9August, 08 2018
10August, 09 2018
11August, 10 2018

<tbody>
</tbody>
Sheet1






on column D i to put wer the ratio will appear
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

fhqwgads

Board Regular
Joined
Jul 17, 2018
Messages
173
hope this works
Code:
Date               	Wash	Detergent	Ratio
August, 01 2018      	6       	4	3:2
August, 02 2018      	5       	3	5:3
August, 03 2018      	4              	2	2:1
D2=
Code:
=B2/GCD(B2,C2)&":"&C2/GCD(B2,C2)
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Actually you Already have your ratios in your Table, Columns B and C.

Now if you want a specific ratio, like "how many washes per unit of detergent" or vice versa, then:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Wash</td><td style=";">Detergent</td><td style=";">Ratio (# Wash to Unit of Detergent)</td><td style=";">Ratio(Unit of Detergent per Wash)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">August, 01 2018</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">0.67</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">August, 02 2018</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1.67</td><td style="text-align: right;;">0.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">August, 03 2018</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">August, 04 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">August, 05 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">August, 06 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">August, 07 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">August, 08 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">August, 09 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">August, 10 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet154</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=ROUND(<font color="Blue">B2/C2,2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=ROUND(<font color="Blue">C2/B2,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

mark692

Active Member
Joined
Feb 27, 2015
Messages
270
Office Version
2016
Platform
Windows
thanks guys, i want to know if the number of detergent is getting lower than the number of wash everyday, do you think by knowing the ratio will help me?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
thanks guys, i want to know if the number of detergent is getting lower than the number of wash everyday, do you think by knowing the ratio will help me?
Column E in my sample is telling you it's getting lower.

Edit: So to clarify, as you can see in Column E, we started with 0.67 unit of detergent per wash, then 0.6, then 0.5
 
Last edited:

mark692

Active Member
Joined
Feb 27, 2015
Messages
270
Office Version
2016
Platform
Windows
Column E in my sample is telling you it's getting lower.

Edit: So to clarify, as you can see in Column E, we started with 0.67 unit of detergent per wash, then 0.6, then 0.5
i see i get it now sir thanks so much!
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
You're welcome.

Looking at your question in Post #4 again, I'm not so sure I understood correctly.

So the downward trend in Column E Only means people are using Less detergent per wash, it doesn't mean that your business is going "downhill", see additional sample (August 4) below:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Date</td><td style=";">Wash</td><td style=";">Detergent</td><td style=";">Ratio (# Wash to Unit of Detergent)</td><td style=";">Ratio(Unit of Detergent per Wash)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">August, 01 2018</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1.5</td><td style="text-align: right;;">0.67</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">August, 02 2018</td><td style="text-align: right;;">5</td><td style="text-align: right;;">3</td><td style="text-align: right;;">1.67</td><td style="text-align: right;;">0.6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">August, 03 2018</td><td style="text-align: right;;">4</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">August, 04 2018</td><td style="text-align: right;;">1000</td><td style="text-align: right;;">375</td><td style="text-align: right;;">2.67</td><td style="text-align: right;;">0.38</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">August, 05 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">August, 06 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">August, 07 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">August, 08 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">August, 09 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">August, 10 2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet154</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=ROUND(<font color="Blue">B2/C2,2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">=ROUND(<font color="Blue">C2/B2,2</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,096,291
Messages
5,449,494
Members
405,568
Latest member
leonsky119

This Week's Hot Topics

Top