Apr 10 2019 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!
Apr 10 2019 06:00 PM
Apr 10 2019 06:07 PM - edited Apr 10 2019 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.
Apr 10 2019 06:12 PM
Apr 10 2019 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.
Apr 10 2019 06:18 PM
Apr 10 2019 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.