Auto Change Number Format based on Value

[ravi2628]

Hi Friends,

Good Morning, Afternoon, Evening, Night.

I need a formula to change the number format based on value.

Book2
	A	B
1	2018	Required Format
2	90,59,801 $	9.06 M$
3	7,74,58,292 $	77.46 M$
4	72,518 $	72.52 K$
5	6,49,64,558 $	64.96 M$
6	6,38,11,998 $	63.81 M$
7	6,26,30,999 $	62.63 M$
8	5,97,96,876 $	59.80 M$
9	5,57,48,878 $	55.75 M$
10	53,64,11,476 $	536.41 M$
11	54,83,198 $	5.48 M$
12	4,77,24,792 $	47.72 M$
13	43,27,40,344 $	432.74 M$
14	46,21,501 $	4.62 M$
15	45,80,207 $	4.58 M$
16	41,07,698 $	4.11 M$
17	3,12,34,452 $	31.23 M$
18	36,50,833 $	3.65 M$
19	35,54,749 $	3.55 M$
20	31,39,004 $	3.14 M$
21	2,81,79,772 $	28.18 M$
22	2,35,18,408 $	23.52 M$
23	2,30,19,762 $	23.02 M$
24	21,48,60,321 $	214.86 M$
25	20,24,55,779 $	202.46 M$
26	26,19,990 $	2.62 M$
27	21,76,375 $	2.18 M$
28	2,13,14,10,354 $	2.13 B$
Sheet1

Cell Formulas
Range	Formula
B2:B28	B2	=IF(LEN(A2)>=10,TEXT(A2,"#0.00,,,")&" B$",IF(LEN(A2)>=7,TEXT(A2,"#0.00,,")&" M$",TEXT(A2,"#0.00,")&" K$"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B28	Cell Value	ending with "K$"	text	NO
B2:B28	Cell Value	ending with "B$"	text	NO
B2:B28	Cell Value	ending with "M$"	text	NO

Currently i was change in the value using the following Condition but i need the same logic on cell Format.

because in the cells have a if & sumifs condition so if i add the logic in the same cell the formula becomes critical to under stand for others.

Thanks in advance.

Regards,
Ravi

 

[bobsan42]

This I did with Conditional formatting only, so you keep the values for calculation:

MrExcel Playbook 02 2021-09.xlsm
	A	B	C
1	2018	Required Format	CF + NF
2	$ 9 059 801.00	9059801.00,, M$	9.06 M$
3	$ 77 458 292.00	77458292.00,, M$	77.46 M$
4	$ 72 518.00	72518.00, K$	72.52 K$
5	$ 64 964 558.00	64964558.00,, M$	64.96 M$
6	$ 63 811 998.00	63811998.00,, M$	63.81 M$
7	$ 62 630 999.00	62630999.00,, M$	62.63 M$
8	$ 59 796 876.00	59796876.00,, M$	59.80 M$
9	$ 55 748 878.00	55748878.00,, M$	55.75 M$
10	$ 536 411 476.00	536411476.00,, M$	536.41 M$
11	$ 5 483 198.00	5483198.00,, M$	5.48 M$
12	$ 47 724 792.00	47724792.00,, M$	47.72 M$
13	$ 432 740 344.00	432740344.00,, M$	432.74 M$
14	$ 4 621 501.00	4621501.00,, M$	4.62 M$
15	$ 4 580 207.00	4580207.00,, M$	4.58 M$
16	$ 4 107 698.00	4107698.00,, M$	4.11 M$
17	$ 31 234 452.00	31234452.00,, M$	31.23 M$
18	$ 3 650 833.00	3650833.00,, M$	3.65 M$
19	$ 3 554 749.00	3554749.00,, M$	3.55 M$
20	$ 3 139 004.00	3139004.00,, M$	3.14 M$
21	$ 28 179 772.00	28179772.00,, M$	28.18 M$
22	$ 23 518 408.00	23518408.00,, M$	23.52 M$
23	$ 23 019 762.00	23019762.00,, M$	23.02 M$
24	$ 214 860 321.00	214860321.00,, M$	214.86 M$
25	$ 202 455 779.00	202455779.00,, M$	202.46 M$
26	$ 2 619 990.00	2619990.00,, M$	2.62 M$
27	$ 500.00	500.00, K$	500.00 $
28	$ 2 131 410 354.00	2131410354.00,,, B$	2.13 B$
Sheet3

Cell Formulas
Range	Formula
C2:C28	C2	=A2:A28
B2:B28	B2	=IF(LEN(A2)>=10,TEXT(A2,"#0.00,,,")&" B$",IF(LEN(A2)>=7,TEXT(A2,"#0.00,,")&" M$",TEXT(A2,"#0.00,")&" K$"))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C28	Cell Value	>=1000000000	text	YES
C2:C28	Cell Value	>=1000000	text	YES
C2:C28	Cell Value	>=1000	text	NO

The when the conditions are met a custom number format is applied depending on the case:

# ##0.00   \ \B$
# ##0.00  \ \M$
# ##0.00 \ \K$

 

[ravi2628]

This I did with Conditional formatting only, so you keep the values for calculation:

MrExcel Playbook 02 2021-09.xlsm
	A	B	C
1	2018	Required Format	CF + NF
2	$ 9 059 801.00	9059801.00,, M$	9.06 M$
3	$ 77 458 292.00	77458292.00,, M$	77.46 M$
4	$ 72 518.00	72518.00, K$	72.52 K$
5	$ 64 964 558.00	64964558.00,, M$	64.96 M$
6	$ 63 811 998.00	63811998.00,, M$	63.81 M$
7	$ 62 630 999.00	62630999.00,, M$	62.63 M$
8	$ 59 796 876.00	59796876.00,, M$	59.80 M$
9	$ 55 748 878.00	55748878.00,, M$	55.75 M$
10	$ 536 411 476.00	536411476.00,, M$	536.41 M$
11	$ 5 483 198.00	5483198.00,, M$	5.48 M$
12	$ 47 724 792.00	47724792.00,, M$	47.72 M$
13	$ 432 740 344.00	432740344.00,, M$	432.74 M$
14	$ 4 621 501.00	4621501.00,, M$	4.62 M$
15	$ 4 580 207.00	4580207.00,, M$	4.58 M$
16	$ 4 107 698.00	4107698.00,, M$	4.11 M$
17	$ 31 234 452.00	31234452.00,, M$	31.23 M$
18	$ 3 650 833.00	3650833.00,, M$	3.65 M$
19	$ 3 554 749.00	3554749.00,, M$	3.55 M$
20	$ 3 139 004.00	3139004.00,, M$	3.14 M$
21	$ 28 179 772.00	28179772.00,, M$	28.18 M$
22	$ 23 518 408.00	23518408.00,, M$	23.52 M$
23	$ 23 019 762.00	23019762.00,, M$	23.02 M$
24	$ 214 860 321.00	214860321.00,, M$	214.86 M$
25	$ 202 455 779.00	202455779.00,, M$	202.46 M$
26	$ 2 619 990.00	2619990.00,, M$	2.62 M$
27	$ 500.00	500.00, K$	500.00 $
28	$ 2 131 410 354.00	2131410354.00,,, B$	2.13 B$
Sheet3

Cell Formulas
Range	Formula
C2:C28	C2	=A2:A28
B2:B28	B2	=IF(LEN(A2)>=10,TEXT(A2,"#0.00,,,")&" B$",IF(LEN(A2)>=7,TEXT(A2,"#0.00,,")&" M$",TEXT(A2,"#0.00,")&" K$"))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C28	Cell Value	>=1000000000	text	YES
C2:C28	Cell Value	>=1000000	text	YES
C2:C28	Cell Value	>=1000	text	NO

The when the conditions are met a custom number format is applied depending on the case:

# ##0.00   \ \B$
# ##0.00  \ \M$
# ##0.00 \ \K$

Thanks bobsan.

you logic worked very well