Dozens in Excel...

[caLLous]

Right, at work (nightclub) we count the stock (bottles and schtuff) in dozens. I'm not really that good in excel (having never really had the need to immerse myself in it before), but I'd like to know how to convert a number into dozens and then perform simple maths on the dozens (adding and subtracting, basically).

For example, if we had 167 bottles of something on one of the bars, I'd put 167 in cell B6 and it would put 13.11 in C6. Would it be easier to do the maths on the normal number and then convert to dozens? I imagine I'm looking for something that would work out how many 12's there are in the number and then put the remainder after the decimal point...?

Any help would be muchly appreciated, because I hate dozens.

TIA, etc.

 

[gmloki]

Pretty much got it with waht you have already done. You can then format cell C6. Fromat>Cells>Number and esnure decimal places=0. This will give you a round number in terms of dozens but it maybe out by 6 bottles either side as it will round up or down for you .

Put your cursor int eh bottom right hand corner of the Cell C6 and you will geta cross. Left click and drag this down all the cells in column C and it will populate them cells with the fomula.

Hope it helps

 

[Scooba da Bass]

That's not what he's asking for, he wants the remaining bottles after the decimal place. You could do it with the cocanate function and conversion to text and back to an integer but I'm not sure how you'll manage it in a single cell, give me a while to play around with it.

Edit; using decimal places = 0 will result in rounding errors, if you use int(formula) it'll always round down.

 

[Mr.Monkey]

Here you go:

Three cells, A1 B1 and C1

A goes the total number of bottles.

B put "=rounddown(A1/12,0)" This will give you the whole dozen

C put "=A1-B1*12" This gives you the remainder (ie incomplete dozen)

 

[Mr.Monkey]

Or if you want to be able to take the numbers after the decimal place (after dividingby 12), just multiply them by 12 to get the whole number of bottles not making up a dozen.

 

[Scooba da Bass]

To do it in one cell use;

=(INT(C1/12))&"."&(C1-(INT(C1/12))*12) where c1 is the cell with the number of bottles in. It'll give you the number of dozens followed by the left over bottles. Any maths is best done before hand.

Edit; Bah, Jup's is slightly more elegant, use that.

 

[Jupitus]

For presentation purposes, without rounding issues, you can use this formula to format the numbers into 'dozens and others':

=TEXT(INT(A1/12),"0")&" dozen and (or whatever text you want dividing the dozens from the extras) "&TEXT(MOD(A1,12),"0")

Where cell A1 contains the actual numerical value. This cell itself will contain text so you cannot do sums with it, but if you had the numbers going down column A you could easily copy and paste this formula down column B to allow easier reading in dozens.

Edit: My version uses the 'MOD' or modulus function to get the remainder after dividing by 12, FYI, but essentially does the same as scooba's

 

[Lazarus]

or have 2 columns :
column 1 showing the number of complete dozens.
Column 2 showing the number left over with the MOD function

 

[caLLous]

Thanks to you all! I did use Jup's Scooba, partly because it's the only one that actually worked. =(INT(C1/12))&"."&(C1-(INT(C1/12))*12) gave me the Excel equivalent of a blank look.

Now, some brain picking about that maths thing...

This is a couple of lines from a stock list as an example:

            | STOCK IN | STOCK 1:30 | EXTRAS | RETURNS |
STELLA B330 |   2.0    |     3.1    |   0.7  |         |
VK BLUE     |   12.0   |            |        |   2.7   |

STOCK IN = Stock that I transfer from the cellar to the bar at the start of the night or stock that I order to the bar during the night because we're running low.

STOCK 1:30 = I fill out a stock list at 1:30 asking for the exact amount of each product that I need.

EXTRAS = After we shut, this is the exact amount of stock that I need (if we sold some of the product between when I do STOCK 1:30 and when we shut).

RETURNS = If I asked for too much at the start of (or during) the night, this is what I send back after we shut.

Now, this is the basic maths part. For Stella B330 in the above example, it would be 2.0 + 3.1 + 0.7 (68) and for VK Blue, 12.0 - 2.7 (113)

I have to fill out Sessional Control sheets to keep track of stock movements and find out which bar variances are occurring on etc. Below is a simplified version of one of these:

            |  OPENING   |  ADDITIONS |  CLOSING   |  CONSUMPTION  |
STELLA B330 | 96  | 8.0  |            | 96  | 8.0  |               |
VK BLUE     | 192 | 16.0 |            | 192 | 16.0 |               |

The above is using the spangly new number > dozen conversion. It's quite self explanatory, but:

OPENING = How much of each product was on the bar at the start of the night.

ADDITIONS = The result of the additions/subtractions on the stock list.

CLOSING = How much of each product was on the bar at the end of the night.

CONSUMPTION = OPENING + ADDITIONS - CLOSING.

Is it doable to put the dozens in the ADDITIONS part in the form of "=2.0+3.1+0.7" or "=12.0-2.7" and have it work the CONSUMPTION out for me? I have a backlog of about 2 months of sessional controls (yay for budget cuts which means I couldn't go in in the daytime and complete them each week!) to get through, and the more automation I can put into it, the better.

TIA again.

 

[Lazarus]

Callous.

FOr your stock in / out, its quite simple. Since you will ALWAYS be subtracting the returns, you can stick in a global forumula :

=(C1*12) + (C2*12) + (C3*12)- (C4*12)

C1 = STOCK IN
C2 = STOCK 1:30
C3 = EXTRAS
C4 = RETURNS

remove the *12 occurences if you only want "number of dozens"

 

[Scooba da Bass]

You can automate the whole thing, the only problem being that the 'Dozens seperator Bottle Remainder' is a text cell, not a numerical cell which means you'll have to fiddle around with changing the contents on the fly.

I'll load up Excel and see if I can work out the easiest way to do it.

 

[Lazarus]

forgive my simplicity callous, but

CONSUMPTION = OPENING + ADDITIONS - CLOSING

is the formula you are looking for. THe consumption column is a simple sum of the other columns

 

[Lazarus]

You can automate the whole thing, the only problem being that the 'Dozens seperator Bottle Remainder' is a text cell, not a numerical cell which means you'll have to fiddle around with changing the contents on the fly.

I'll load up Excel and see if I can work out the easiest way to do it.

one of the rules to follow in excel is, if you are going to use a cell for calculation, leave it as a number.

 

[caLLous]

Yes laz, this is what I've been using for the past 4 months. But I've been having to convert dozens into numbers before I do it and it would be that much easier if the spreadsheet could just perform the calculation on the dozens rather than having to convert.

 

[Clown]

I'm sure glad I did my CLAIT!!

 

[Arya]

Ever wondered why the rest of europe uses the metric system ?

 

[Clown]

beCuz U did teh CLaiit!!?

 

[caLLous]

Heh, I was wondering how long it would be before someone brought that up. It works well because a case = 2 dozen and when the stock take is done every week we count the number of cases and double it. And head office says we have to.

 

[Mr.Monkey]

Sue head office for dealing in non metric units.

Become a Metric Marytr.
Or burn some more puppies.

 

[Lazarus]

Yes laz, this is what I've been using for the past 4 months. But I've been having to convert dozens into numbers before I do it and it would be that much easier if the spreadsheet could just perform the calculation on the dozens rather than having to convert.

it would probably be simpler to calculate on the numbers of bottles and THEN convert to dozens. This would be more accurate and could be done quite easily, even by using a hidden sheet

<edit> ill expand the above.

2.7 = 24 + 8.4 bottles which is not condusive to accurate calculation

 

[caLLous]

   |   A   |   B   |   C   |   D   |   E   |   F   |   G   |   H   |   I   |
1  |       |    OPENING    |   ADDITIONS   |    CLOSING    |  CONSUMPTION  |  
2  |       |   #   | dozen |   #   | dozen |   #   | dozen |   #   | dozen |
3  |Stella |  96   |  8.0  |       |       |  96   |  8.0  |       |       |

(I hope the above makes sense)

For the CONSUMPTION, I can do =B3+D3-F3 in H3 and then =TEXT(INT(H3/12),"0")&"."&TEXT(MOD(H3,12),"0") in I3. But the laziness inside me wants to know if it's possible to work out the additions by adding/subtractin dozens rather than having to convert to numbers first. I *could* convert to numbers beforehand without any problem (I've been doing it since these damn Sessional Controls were implemented) but it would be nice if it would do it for me.

 

[Lazarus]

Callous,
reread your first post.

I think I have found something to help

Cell 1 (Total number of FULL dozens)
=FLOOR(A1/12,1) where cell A1 = number of bottles

Cell 2 (Remainder)
=MOD(A1,12) = where cell A1 = number of bottles.

Cell 3
+cell1&"."&cell2

each cell must be reformatted :

select cells using pointer.
right click
format cells
select number tab
scroll down and select custom
in the scroll screen on the right (under "Type") select the @ option click OK

this allows the number to be treated as text but can still be calculated as a number.

it will take 57 bottles and return 4.9

4 Dozen + 9 remainng bottles.

however to allow further calculations, you must calculate based on number of bottles. 4.9 to excel is decimal :

4.3 (51 bottles) + 2.7 (31 bottles) would add up to 7 [84] (not 82 bottles)

simple enough to use a hidden cell to convery/calculate as bottles and convert back to dozens for display purposes.

 

[Jupitus]

Callous:

just a quick couple of checks... you want to be able to enter the stock numbers easily in terms of dozens and bottles, and have the displayed results in this form. When you enter, say, 3 dozen and 3 bottles, would you be happy if that meant typing '3.03' or is that too awkward (I mean the leading '0' after the '.') ?

 

[Summo]

Madness!

 

[caLLous]

Thanks for the constructive, well formed and helpful reply Summo!

Jup, 3.03 isn't a problem at all if it's causing problems without the leading 0.

 

[Jupitus]

Ok, here is my suggestion. Cells where you will input values are input as I just described above in 'pseudo dozens'. The following formula will give you a number of bottles from this:

=(INT(G13)*12)+(MOD(G13,1)*100)

where G13 contains the input number in your pseudo dozens input format. A very small change in my previous formula to this:

=TEXT(INT(A1/12),"0")&"."&TEXT(MOD(A1,12),"00")

will take any number of bottles and present it in 'pseudo dozens'. This means you can have 8 columns alternating pseudo dozens and corresponding numbers. Hide the columns that are showing numbers but still use them in your calculation, and just display the results with the formula applied above.

EDIT:
A bit clearer... type the following into cells as detailed to see clearly what I mean

A2: 11.03
B2: =(INT(A2)*12+MOD(A2,1)*100)
C2: 4
D2: =INT(C2)*12+MOD(C2,1)*100
E2: 3.07
F2: =(INT(E2)*12)+(MOD(E2,1)*100)
G2: =TEXT(INT(H2/12),"0")&"."&TEXT(MOD(H2,12),"00")
H2: =B2+D2-F2

Then hide columns B,D,F and H. You input your open values in A2, added in C2, closing (using the 3.07 to indicate 3 dozen and 7 bottles) in E2 and the result is in G2 displayed as you want it. You could do other claculations in a similar fashion.

 

[~Yuckfou~]

'kin geeks tbh

 

[GDW]

Im scared

 

[caLLous]

I feel like a bit of a twat but apologies to Scooba, yours worked fine. For some reason Excel was putting the results in a white font on a white background so it looked like there was nothing there. ;(

Mountains of thanks to Jup, that works exactly as I originally pictured it.

 

[Jupitus]

NP CaLLous, I'll be stealing that pint you claimed off someone in another thread, somewhere..... :kissit: