Copy pasting issue

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
Hey guys, i have an issue i havent been able to figure out

I have a table that displays a year split into 12 months, I'm drawing graphs for each of the months
My table is not full of data, some days are just empty. When i try to create graphs out of them sometimes they bug out, they never do if i leave no empty spaces but fill them with 0's.

So my issue is, my single cells looks like this: 192 / 7 avg = 27
I only care about the 27 part, so i make a replica of the table with just right(cell, 2) and have the table i want to make graphs out of.

But, i would like to fill all the empty fields with 0's.

I could make a cycle that goes through the cells that checks 1 by 1 and if it doesn't have a value just make it 0

But i would like to keep the pointless computations to a minimum

If i take the replicated table, copy it, paste it in as values and convert it to numbers i can use the data, BUT all the empty cells that i copy pasted, are not actually blank anymore and I'm unable to make any quick selects and fill them with 0

Any ideas how to find the empty, non blank cells? Or maybe my whole approach of right(cells,2), copy paste can be done different
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,159
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
you shouldn't be using right(cells,2) unless all the cells are less than 100.
=round(cells,0) is a better choice.

you can create a replicate table with

=IF(ISBLANK(A1),0,ROUND(A1,0))

like this

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">20.66667</td><td style="text-align: right;;">26.33333</td><td style="text-align: right;;">6.333333</td><td style="text-align: right;;">15.66667</td><td style="text-align: right;;">21.33333</td><td style="text-align: right;;"></td><td style="text-align: right;;">21</td><td style="text-align: right;;">26</td><td style="text-align: right;;">6</td><td style="text-align: right;;">16</td><td style="text-align: right;;">21</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">23</td><td style="text-align: right;;">5.666667</td><td style="text-align: right;;">23.66667</td><td style="text-align: right;;">11.66667</td><td style="text-align: right;;">12.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td><td style="text-align: right;;">6</td><td style="text-align: right;;">24</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">18</td><td style="text-align: right;;">1.333333</td><td style="text-align: right;;">19.66667</td><td style="text-align: right;;">15.66667</td><td style="text-align: right;;">23.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;">18</td><td style="text-align: right;;">1</td><td style="text-align: right;;">20</td><td style="text-align: right;;">16</td><td style="text-align: right;;">24</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">8.333333</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.333333</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">3</td><td style="text-align: right;;">10</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">22.33333</td><td style="text-align: right;;">30.33333</td><td style="text-align: right;;"></td><td style="text-align: right;;">24</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">22</td><td style="text-align: right;;">30</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">19.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.666667</td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">19.66667</td><td style="text-align: right;;">10.33333</td><td style="text-align: right;;">2</td><td style="text-align: right;;">15.66667</td><td style="text-align: right;;">14.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;">20</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2</td><td style="text-align: right;;">16</td><td style="text-align: right;;">15</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">15.66667</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8.333333</td><td style="text-align: right;;">29.33333</td><td style="text-align: right;;">22.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;">16</td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td><td style="text-align: right;;">29</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">6</td><td style="text-align: right;;">4.333333</td><td style="text-align: right;;">10.66667</td><td style="text-align: right;;">16</td><td style="text-align: right;;">10.66667</td><td style="text-align: right;;"></td><td style="text-align: right;;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">11</td><td style="text-align: right;;">16</td><td style="text-align: right;;">11</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">1.666667</td><td style="text-align: right;;">2</td><td style="text-align: right;;">14</td><td style="text-align: right;;">28.66667</td><td style="text-align: right;;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">14</td><td style="text-align: right;;">29</td><td style="text-align: right;;">33</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G1</th><td style="text-align:left">=IF(<font color="Blue">ISBLANK(<font color="Red">A1</font>),0,ROUND(<font color="Red">A1,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

Lavina

Board Regular
Joined
Dec 18, 2018
Messages
75
My cells contain string data (192 / 7 avg = 27), that's why I use the right function. But your idea was great, does exactly what i need, thanks! :)
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,159
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
My cells contain string data (192 / 7 avg = 27), that's why I use the right function. But your idea was great, does exactly what i need, thanks! :)

i should had read your post properly!
anyway, glad that you got what you're after.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,789
Messages
5,524,885
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top