Formula to add a comma to a number as well as a decimal even when .0

Question

I am pulling information from one tab (Tab name is Stats and the cell is formatted as a number) to another (Tab name is Stat FS). I need the formula to carry over the decimal even if it's a .0, but not to add a .0 if none exists.&nbsp;Example:&nbsp;Stats-GHIJKLMNOPQRSTUVWXYZCOMP %PASS YDSPASS TDINTATTRUSH YDSYDS/ATTRUSH TDRECREC YDSYDS/RECREC TDSOLOASTTOTALTFLSACKSINTPASS DEFFF&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;36367212.57003612221166632744.36&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;89107812.18&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;118133411.319&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;484729.87&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;28235130150&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6198416.111&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;60.928681891847544.115&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6587313.45&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;24265010.57010&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5626829.50261&nbsp;Stat FS (What it actually reads)-IJKLMNOPQRSTUVWXCAT 1STAT 1CAT 2STAT 2CAT 3STAT 3CAT 4STAT 4CAT 5STAT 5CAT 6STAT 6CAT 7STAT 7CAT 8STAT 8SOLO36AST36TOTAL72TFL12.5SACKS7INT0PASS DEF0FF3COMP %61PASS YDS2221PASS TD16INT6RUSH YDS274RUSH TD60000REC89REC YDS1078YDS/REC12.1REC TD8&nbsp;0&nbsp;0&nbsp;0&nbsp;0REC118REC YDS1334YDS/REC11.31REC TD9&nbsp;0&nbsp;0&nbsp;0&nbsp;0REC48REC YDS472YDS/REC9.8REC TD7&nbsp;0&nbsp;0&nbsp;0&nbsp;0SOLO28AST23TOTAL51TFL3SACKS0INT1PASS DEF5FF0REC61REC YDS984YDS/REC16.1REC TD11&nbsp;0&nbsp;0&nbsp;0&nbsp;0COMP %60.9PASS YDS2868PASS TD18INT9RUSH YDS754RUSH TD150000REC65REC YDS873YDS/REC13.4REC TD5&nbsp;0&nbsp;0&nbsp;0&nbsp;0SOLO24AST26TOTAL50TFL10.5SACKS7INT0PASS DEF1FF0#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;SOLO56AST26TOTAL82TFL9.5SACKS0INT2PASS DEF6FF1&nbsp;&nbsp;&nbsp;&nbsp;Stat FS (What I need it to read)-IJKLMNOPQRSTUVWXCAT 1STAT 1CAT 2STAT 2CAT 3STAT 3CAT 4STAT 4CAT 5STAT 5CAT 6STAT 6CAT 7STAT 7CAT 8STAT 8SOLO36AST36TOTAL72TFL12.5SACKS7.0INT0PASS DEF0FF3COMP %61.0PASS YDS2,221PASS TD16INT6RUSH YDS274RUSH TD60000REC89REC YDS1,078YDS/REC12.1REC TD8&nbsp;0&nbsp;0&nbsp;0&nbsp;0REC118REC YDS1,334YDS/REC11.31REC TD9&nbsp;0&nbsp;0&nbsp;0&nbsp;0REC48REC YDS472YDS/REC9.8REC TD7&nbsp;0&nbsp;0&nbsp;0&nbsp;0SOLO28AST23TOTAL51TFL3.0SACKS0INT1PASS DEF5FF0REC61REC YDS984YDS/REC16.1REC TD11&nbsp;0&nbsp;0&nbsp;0&nbsp;0COMP %60.9PASS YDS2,868PASS TD18INT9RUSH YDS754RUSH TD150000REC65REC YDS873YDS/REC13.4REC TD5&nbsp;0&nbsp;0&nbsp;0&nbsp;0SOLO24AST26TOTAL50TFL10.5SACKS7.0INT0PASS DEF1FF0#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;#N/A&nbsp;SOLO56AST26TOTAL82TFL9.5SACKS0INT2PASS DEF6FF1&nbsp;&nbsp;&nbsp;&nbsp;An example of the formula I am using is:=IF(D12="QB",Stats!G12,IF(D12="RB",Stats!K12,IF(SUM(COUNTIF(D12,{"WR","TE","FB"})),Stats!O12,IF(SUM(COUNTIF(D12,{"EDGE","DL","LB","CB","S"})),Stats!S12,""))))&nbsp;&nbsp;I've been told I need another if and a MOD, but for the life of me, I'm not sure exactly where or how to use that. I need everything from the Stat FS tab to read exactly how it does in the Stats tab, so a .0 when there is a .0 and have a comma in numbers that are in the thousands. Can anyone point me in the right direction on this? Thanks!&nbsp;&nbsp;

twifoo · Answer

I haven’t tested, but try this custom format:#,##0.0#

cpavl1113 · Answer

Twifoo&nbsp;Unfortunately that added a decimal point to every cell. I just need it in the cells that originally have decimal points. The .1, .2, .3 etc show up, but the .0 do not.&nbsp;Attached is the document, if you want to see the entire thing.

twifoo · Answer

Modify the custom format to this:#,##0.##

cpavl1113 · Answer

Twifoo&nbsp;That adds a decimal point to everything, but no .0 where I need it and decimal points in cells that I don't need it.

twifoo · Answer

Try the General format.

Forum Discussion

Formula to add a comma to a number as well as a decimal even when .0

6 Replies

Resources

G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
COMP %	PASS YDS	PASS TD	INT	ATT	RUSH YDS	YDS/ATT	RUSH TD	REC	REC YDS	YDS/REC	REC TD	SOLO	AST	TOTAL	TFL	SACKS	INT	PASS DEF	FF
												36	36	72	12.5	7	0	0	3
61	2221	16	6	63	274	4.3	6
								89	1078	12.1	8
								118	1334	11.31	9
								48	472	9.8	7
												28	23	51	3	0	1	5	0
								61	984	16.1	11
60.9	2868	18	9	184	754	4.1	15
								65	873	13.4	5
												24	26	50	10.5	7	0	1	0



												56	26	82	9.5	0	2	6	1

I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
CAT 1	STAT 1	CAT 2	STAT 2	CAT 3	STAT 3	CAT 4	STAT 4	CAT 5	STAT 5	CAT 6	STAT 6	CAT 7	STAT 7	CAT 8	STAT 8
SOLO	36	AST	36	TOTAL	72	TFL	12.5	SACKS	7	INT	0	PASS DEF	0	FF	3
COMP %	61	PASS YDS	2221	PASS TD	16	INT	6	RUSH YDS	274	RUSH TD	6	0	0	0	0
REC	89	REC YDS	1078	YDS/REC	12.1	REC TD	8		0		0		0		0
REC	118	REC YDS	1334	YDS/REC	11.31	REC TD	9		0		0		0		0
REC	48	REC YDS	472	YDS/REC	9.8	REC TD	7		0		0		0		0
SOLO	28	AST	23	TOTAL	51	TFL	3	SACKS	0	INT	1	PASS DEF	5	FF	0
REC	61	REC YDS	984	YDS/REC	16.1	REC TD	11		0		0		0		0
COMP %	60.9	PASS YDS	2868	PASS TD	18	INT	9	RUSH YDS	754	RUSH TD	15	0	0	0	0
REC	65	REC YDS	873	YDS/REC	13.4	REC TD	5		0		0		0		0
SOLO	24	AST	26	TOTAL	50	TFL	10.5	SACKS	7	INT	0	PASS DEF	1	FF	0
#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A
#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A
#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A		#N/A
SOLO	56	AST	26	TOTAL	82	TFL	9.5	SACKS	0	INT	2	PASS DEF	6	FF	1