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
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,350
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


Book1
ABCDEFGHIJK
120.6666726.333336.33333315.6666721.33333212661621
2235.66666723.6666711.6666712.66667236241213
3181.33333319.6666715.6666723.66667181201624
48.3333333.33333310800310
52422.3333330.3333324002230
619.6666720.666667200021
719.6666710.33333215.6666714.66667201021615
815.6666718.33333329.3333322.6666716182923
964.33333310.666671610.6666764111611
101.66666721428.666673322142933
Sheet1
Cell Formulas
RangeFormula
G1=IF(ISBLANK(A1),0,ROUND(A1,0))
 
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,350
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,874
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top