Hi Friends,
Good Morning, Afternoon, Evening, Night.
I need a formula to change the number format based on value.
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
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