04102019 01:23 PM
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
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 
Stat FS (What it actually reads)
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

Stat FS (What I need it to read)
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.0  INT  0  PASS DEF  0  FF  3 
COMP %  61.0  PASS YDS  2,221  PASS TD  16  INT  6  RUSH YDS  274  RUSH TD  6  0  0  0  0 
REC  89  REC YDS  1,078  YDS/REC  12.1  REC TD  8  0  0  0  0  
REC  118  REC YDS  1,334  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.0  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  2,868  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.0  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

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!
04102019 06:00 PM
04102019 06:07 PM  edited 04102019 06:08 PM
@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.
04102019 06:12 PM
04102019 06:14 PM
@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.
04102019 06:18 PM
04102019 06:20 PM
@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.
by hynguyen on July 11, 2020
by cpavl1113 on April 10, 2019
by Gintaras Pacevičius on March 02, 2018