Custom number/decimal

[JimmyBigDog]

Hi,

(I've gone through a lot of threads and couldn't find a similar question)

I work with numbers split into "Units" and "Baskets".

24 Units = 1 Basket

Can a formula convert (e.g.)
25 units into 1 basket and 1 unit.
42 units into 1 basket and 18 units

420 units into 17 baskets and 12 units ...and displayed as "17.12" or "17+12"?


Any help appreciated,
Jimmy

 

[Brian from Maui]

Try

=INT(A1/24)&" basket(s) " &MOD(A1,24)&" Unit(s)"

 

[shg]

Another way:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]

25​

[/td][td]

1.01​

[/td][td]B1: =DOLLARFR(A1/24, 24)[/td][/tr]
[tr][td]

2​

[/td][td]

42​

[/td][td]

1.18​

[/td][td][/td][/tr]
[tr][td]

3​

[/td][td]

420​

[/td][td]

17.12​

[/td][td][/td][/tr]
[/table]

 

[Rick Rothstein]

Try

=INT(A1/24)&" basket(s) " &MOD(A1,24)&" Unit(s)"

I think he wants it to look like this...

=INT(A1/24)&"."&MOD(A1,24)

or like this...

=INT(A1/24)&"+"&MOD(A1,24)

instead.

 

[ImranMalek]

Hi Jimmy,
Why not use a simple formula

=QUOTIENT(A1,24)&"."&MOD(A1,24)

Considering A1 has 420 units it will give you 17.12 as answer.
Hope it helps.

 

[shg]

=INT(A1/24)&"."&MOD(A1,24)

That makes 25 and 34 baskets ambiguous if you were to convert the result to a number.

 

[SpillerBD]

=DOLLARFR(TargetAmount/ 24 , 24)

Just be careful how you use this number. or reverse with DOLLARDE

 

[Rick Rothstein]

That makes 25 and 34 baskets ambiguous if you were to convert the result to a number.

Yeah, the decimal point method allows for that confusion. If the OP wants the decimal point instead of the plus sign, then this would work...

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

Still, I like your formula in Message #3 better.

 

[JimmyBigDog]

Thanks for the replies guys- the below works just fine and will be easy for me to convert and maintain.

Thanks again,
Jimmy

Another way:

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

25​

[/TD]
[TD]

1.01​

[/TD]
[TD]B1: =DOLLARFR(A1/24, 24)[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

42​

[/TD]
[TD]

1.18​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

420​

[/TD]
[TD]

17.12​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[shg]

Many hands make light work. You're welcome.