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

Copper Contributor

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.

 

Example:

 

Stats-

GHIJKLMNOPQRSTUVWXYZ
COMP %PASS YDSPASS TDINTATTRUSH YDSYDS/ATTRUSH TDRECREC YDSYDS/RECREC TDSOLOASTTOTALTFLSACKSINTPASS DEFFF
            36367212.57003
612221166632744.36            
        89107812.18        
        118133411.319        
        484729.87        
            28235130150
        6198416.111        
60.928681891847544.115            
        6587313.45        
            24265010.57010
                    
                    
                    
            5626829.50261

 

Stat FS (What it actually reads)-

IJKLMNOPQRSTUVWX
CAT 1STAT 1CAT 2STAT 2CAT 3STAT 3CAT 4STAT 4CAT 5STAT 5CAT 6STAT 6CAT 7STAT 7CAT 8STAT 8
SOLO36AST36TOTAL72TFL12.5SACKS7INT0PASS DEF0FF3
COMP %61PASS YDS2221PASS TD16INT6RUSH YDS274RUSH TD60000
REC89REC YDS1078YDS/REC12.1REC TD8 0 0 0 0
REC118REC YDS1334YDS/REC11.31REC TD9 0 0 0 0
REC48REC YDS472YDS/REC9.8REC TD7 0 0 0 0
SOLO28AST23TOTAL51TFL3SACKS0INT1PASS DEF5FF0
REC61REC YDS984YDS/REC16.1REC TD11 0 0 0 0
COMP %60.9PASS YDS2868PASS TD18INT9RUSH YDS754RUSH TD150000
REC65REC YDS873YDS/REC13.4REC TD5 0 0 0 0
SOLO24AST26TOTAL50TFL10.5SACKS7INT0PASS DEF1FF0
#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 
SOLO56AST26TOTAL82TFL9.5SACKS0INT2PASS DEF6FF

1

 

 

 

 

Stat FS (What I need it to read)-

IJKLMNOPQRSTUVWX
CAT 1STAT 1CAT 2STAT 2CAT 3STAT 3CAT 4STAT 4CAT 5STAT 5CAT 6STAT 6CAT 7STAT 7CAT 8STAT 8
SOLO36AST36TOTAL72TFL12.5SACKS7.0INT0PASS DEF0FF3
COMP %61.0PASS YDS2,221PASS TD16INT6RUSH YDS274RUSH TD60000
REC89REC YDS1,078YDS/REC12.1REC TD8 0 0 0 0
REC118REC YDS1,334YDS/REC11.31REC TD9 0 0 0 0
REC48REC YDS472YDS/REC9.8REC TD7 0 0 0 0
SOLO28AST23TOTAL51TFL3.0SACKS0INT1PASS DEF5FF0
REC61REC YDS984YDS/REC16.1REC TD11 0 0 0 0
COMP %60.9PASS YDS2,868PASS TD18INT9RUSH YDS754RUSH TD150000
REC65REC YDS873YDS/REC13.4REC TD5 0 0 0 0
SOLO24AST26TOTAL50TFL10.5SACKS7.0INT0PASS DEF1FF0
#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 
SOLO56AST26TOTAL82TFL9.5SACKS0INT2PASS DEF6FF

1

 

 

 

 

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,""))))

 

 

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!

 

 

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

@Twifoo 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.

 

Attached is the document, if you want to see the entire thing.

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

@Twifoo 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 It's already at the general format. For some reason, the general format doesn't pull exact data the way it was entered from another cell. Not sure why. I would think that if I have a formula that pulls data from another cell, it would copy that data exactly, but it does not.