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.