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:

Row\Col	A​	B​	C​
1​	25​	1.01​	B1: =DOLLARFR(A1/24, 24)
2​	42​	1.18​
3​	420​	17.12​

 

[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:

Row\Col	A​	B​	C​
1​	25​	1.01​	B1: =DOLLARFR(A1/24, 24)
2​	42​	1.18​
3​	420​	17.12​

<tbody>
</tbody>

 

[shg]

Many hands make light work. You're welcome.