Copy pasting issue

[Lavina]

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

 

[AlanY]

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
	A	B	C	D	E	F	G	H	I	J	K
1	20.66667	26.33333	6.333333	15.66667	21.33333		21	26	6	16	21
2	23	5.666667	23.66667	11.66667	12.66667		23	6	24	12	13
3	18	1.333333	19.66667	15.66667	23.66667		18	1	20	16	24
4	8.333333			3.333333	10		8	0	0	3	10
5	24			22.33333	30.33333		24	0	0	22	30
6	19.66667			2	0.666667		20	0	0	2	1
7	19.66667	10.33333	2	15.66667	14.66667		20	10	2	16	15
8	15.66667	1	8.333333	29.33333	22.66667		16	1	8	29	23
9	6	4.333333	10.66667	16	10.66667		6	4	11	16	11
10	1.666667	2	14	28.66667	33		2	2	14	29	33
Sheet1

Cell Formulas
Range	Formula
G1	=IF(ISBLANK(A1),0,ROUND(A1,0))

 

[Lavina]

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]

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.