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.