I inventory cases of beverages that come 24 cans to a case. How can I format
cells so that 0.24 equals 1 case.
Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

Thanks in advance for any help.

Hi,
I am assuming you have totals per column so you can enter a formula like this

=SUM(a7:a50)/0.24

In your 2nd example I get 2.08 no 2.2

"Danny D" wrote:

I inventory cases of beverages that come 24 cans to a case. How can I format
cells so that 0.24 equals 1 case.
Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

Thanks in advance for any help.

Danny,

=INT(SUM(A2:A10)/0.24) & " Cases and " & MOD(SUM(A2:A10)*100,24) & " cans"


Or

=INT(SUM(A2:A10)/0.24) + MOD(SUM(A2:A10)*100,24) /100

where the part after the decimal is the number of cans, akin to your .12 being half a case...

HTH,
Bernie
MS Excel MVP


"Danny D" <Danny wrote in message
...
I inventory cases of beverages that come 24 cans to a case. How can I format
cells so that 0.24 equals 1 case.
Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

Thanks in advance for any help.

On Jan 27, 8:43*am, Danny D <Danny
wrote:
I inventory cases of beverages that come 24 cans to a
case. How can I format cells so that 0.24 equals 1 case.

"Format", or change the value? I assume you mean the latter.

Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

I think the last example should result in 2.02, if your form is x.yy,
where "x" is number of cases and "yy" is number of cans. For example,
I am interpreting ".20" as 20 cans, not 2 cans. Note that 0.2 and
0.20 are identical numerically.

If you insist on recording individual counts in the form "x.yy", note
that no solution involving simply SUM(range)/0.24 will be correct.
Consider 5 entries of 0.23 (5 cases with 23 cans each). I think the
answer you would like is 4.19 (4 24-can cases and 19 cans).

If you insist on recording each count in the form "x.yy", the
following might work for you:

=SUMPRODUCT(INT(A1:A5))
+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
& "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00")

Note that that results in text, not a number, which you can format
with right horizontal alignment. If you want a number, there are many
ways to get that. One way:

=--(SUMPRODUCT(INT(A1:A5))
+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
& "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00"))

Note: I am using SUMPRODUCT to avoid using an array formula. Think
of it as "sum". If it helps you to understand "A1:A5*100", you can
write it as "(A1:A5)*100".

A word of caution: decimal fraction are not stored internally exactly
as we see them in Excel. You might encounter some suprising numerical
anomalies.

Since you are apparently inventory cans, not cases, I would suggest
that you maintain a count of cans (integers).

Then, if you want the result to be represented in terms of "x.yy" as
defined above, one of the following should work for you:

=int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00")

=--(int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00"))

HTH.

PS....

On Jan 27, 10:52 am, I wrote:
If you want a number [...]:

=--(SUMPRODUCT(INT(A1:A5))
+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
& "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00"))

[... and for a different method ...]

=--(int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00"))

Although I'm not sure I would trust it in general, I discovered
empirically that the following seems to provide identical results
(i.e. the same internal representation) even for a large number of
cases:

When the count is "x.yy" (cases and cans):

=SUMPRODUCT(INT(A1:A5))
+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
+ MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24)/100

When the count is simply cans:

=int(sum(A1:A5)/24) + mod(sum(A1:A5),24)/100

It might be prudent to embed the entire formula (whichever you use) in
a ROUND(...,2) function call, just to be sure that WYSIWYG.


----- original posting -----

On Jan 27, 10:52*am, joeu2004 wrote:
On Jan 27, 8:43*am, Danny D <Danny
wrote:

I inventory cases of beverages that come 24 cans to a
case. How can I format cells so that 0.24 equals 1 case.

"Format", or change the value? *I assume you mean the latter.

Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

I think the last example should result in 2.02, if your form is x.yy,
where "x" is number of cases and "yy" is number of cans. *For example,
I am interpreting ".20" as 20 cans, not 2 cans. *Note that 0.2 and
0.20 are identical numerically.

If you insist on recording individual counts in the form "x.yy", note
that no solution involving simply SUM(range)/0.24 will be correct.
Consider 5 entries of 0.23 (5 cases with 23 cans each). *I think the
answer you would like is 4.19 (4 24-can cases and 19 cans).

If you insist on recording each count in the form "x.yy", the
following might work for you:

=SUMPRODUCT(INT(A1:A5))
*+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
*& "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00")

Note that that results in text, not a number, which you can format
with right horizontal alignment. *If you want a number, there are many
ways to get that. *One way:

=--(SUMPRODUCT(INT(A1:A5))
* * + INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
* * & "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00"))

Note: *I am using SUMPRODUCT to avoid using an array formula. *Think
of it as "sum". *If it helps you to understand "A1:A5*100", you can
write it as "(A1:A5)*100".

A word of caution: *decimal fraction are not stored internally exactly
as we see them in Excel. *You might encounter some suprising numerical
anomalies.

Since you are apparently inventory cans, not cases, I would suggest
that you maintain a count of cans (integers).

Then, if you want the result to be represented in terms of "x.yy" as
defined above, one of the following should work for you:

=int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00")

=--(int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00"))

HTH.

Errata....

On Jan 27, 10:52 am, I wrote:
If you insist on recording individual counts in the form
"x.yy", note that no solution involving simply
SUM(range)/0.24 will be correct. Consider 5 entries of
0.23 (5 cases with 23 cans each). I think the answer you
would like is 4.19 (4 24-can cases and 19 cans).

And a correct SUM(range)/0.24 solution __will__ work in that case. It
would work whenever __all__ of the values in the range are less than
1, as in the OP's examples.

Klunk! I had started with a different example, then at the last
minute, "simplified" it to the point where my statement is wrong.

The point is: in "x.yy", "x" represents units of 24. So consider an
example of 5 entries of 1 case plus 23 cans. I assume each entry
would be recorded as 1.23. That represents a total of 235 cans. The
sum should be 9.19, not 25.15 -- the result of a SUM(range)/0.24
solution.


----- original posting -----

On Jan 27, 10:52*am, joeu2004 wrote:
On Jan 27, 8:43*am, Danny D <Danny
wrote:

I inventory cases of beverages that come 24 cans to a
case. How can I format cells so that 0.24 equals 1 case.

"Format", or change the value? *I assume you mean the latter.

Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

I think the last example should result in 2.02, if your form is x.yy,
where "x" is number of cases and "yy" is number of cans. *For example,
I am interpreting ".20" as 20 cans, not 2 cans. *Note that 0.2 and
0.20 are identical numerically.

If you insist on recording individual counts in the form "x.yy", note
that no solution involving simply SUM(range)/0.24 will be correct.
Consider 5 entries of 0.23 (5 cases with 23 cans each). *I think the
answer you would like is 4.19 (4 24-can cases and 19 cans).

If you insist on recording each count in the form "x.yy", the
following might work for you:

=SUMPRODUCT(INT(A1:A5))
*+ INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
*& "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00")

Note that that results in text, not a number, which you can format
with right horizontal alignment. *If you want a number, there are many
ways to get that. *One way:

=--(SUMPRODUCT(INT(A1:A5))
* * + INT(SUMPRODUCT(MOD(A1:A5*100,100))/24)
* * & "." & TEXT(MOD(SUMPRODUCT(MOD(A1:A5*100,100)),24),"00"))

Note: *I am using SUMPRODUCT to avoid using an array formula. *Think
of it as "sum". *If it helps you to understand "A1:A5*100", you can
write it as "(A1:A5)*100".

A word of caution: *decimal fraction are not stored internally exactly
as we see them in Excel. *You might encounter some suprising numerical
anomalies.

Since you are apparently inventory cans, not cases, I would suggest
that you maintain a count of cans (integers).

Then, if you want the result to be represented in terms of "x.yy" as
defined above, one of the following should work for you:

=int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00")

=--(int(sum(A1:A5)/24) & "." & text(mod(sum(A1:A5),24),"00"))

HTH.

You can use the DOLLARDE and DOLLARFR functions in the Analysis
ToolPak. (Go to the Tools menu, choose Add-Ins, and ensure that
Analysis ToolPak is checked).

Suppose your values in the format of Case.Bottles (e.g., 1.12
indicates 1 case of 24 + 12 bottles) is in A1:A4.

In cell B1, enter =DOLLARDE(A1,24) and fill down through B1:B4.

Then to compute the sum in B5, enter

=DOLLARFR(SUM(B1:B3),24)

This will return the result in the cases.bottles format (e.g., 6.06
indicates 6 cases, 6 bottles).

In your original data, you must include a leading zero for the number
of bottles less than 10. That is, for 5 cases 3 bottles, you must
enter 5.03 not 5.3 (5.3 would be treated as 5 cases, 30 bottles).

If you want your sum in standard decimal notation, just use the SUM
function. =SUM(B1:B4). This will return 6.5 to indicate 6 cases 12
bottles.

The DOLLARDE and DOLLARFR functions take their name for their original
usage purpose with was to allow you to enter dollar amouts as dollars
and eighths of dollars, as in stock prices (5 1/8 =5.1), but they can
be used to manipulate any quantitative in which the fractional portion
is not based on 10.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Tue, 27 Jan 2009 08:43:01 -0800, Danny D <Danny
wrote:

I inventory cases of beverages that come 24 cans to a case. How can I format
cells so that 0.24 equals 1 case.
Examples:
0.12+0.12=1 instead of 0.24
0.12+0.18+.20=2.2 instead of 0.50

Thanks in advance for any help.