RE: paste formula and skip blanks

[Sean]

dear all,

I want to paste the formula, but skip the blanks. Here is an example,

12 2 (=round(A1/7,0))
13 2
15 2
0
0
0
0
0
0
0

the second column is the formula. after calculating in B1 (round(A1/7,
0)), I copy B1 and then paste the whole B column. Instead of only
giving me 3 observations (B1~B3), excel also gives me zeros in the rest
of the cells. How do I ask excel not to paste these zeros?

Thank you for your help.

Sean

[Teodomiro]

Would an IF statement work for you?

=IF(A1=0,"",round(A1/7,0))

The formula will still occupy the cell, but nothing will be displayed if the corresponding cell in column A is zero.

dear all,

I want to paste the formula, but skip the blanks. Here is an example,

12 2 (=round(A1/7,0))
13 2
15 2
0
0
0
0
0
0
0

the second column is the formula. after calculating in B1 (round(A1/7,
0)), I copy B1 and then paste the whole B column. Instead of only
giving me 3 observations (B1~B3), excel also gives me zeros in the rest
of the cells. How do I ask excel not to paste these zeros?

Thank you for your help.

Sean

[Peo Sjoblom]

=IF(A1="","",ROUND(A1/7,0))


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Sean" <[email protected]> wrote in message
news:[email protected]...
> dear all,
>
> I want to paste the formula, but skip the blanks. Here is an example,
>
> 12 2 (=round(A1/7,0))
> 13 2
> 15 2
> 0
> 0
> 0
> 0
> 0
> 0
> 0
>
> the second column is the formula. after calculating in B1 (round(A1/7,
> 0)), I copy B1 and then paste the whole B column. Instead of only
> giving me 3 observations (B1~B3), excel also gives me zeros in the rest
> of the cells. How do I ask excel not to paste these zeros?
>
> Thank you for your help.
>
> Sean
>

[Crowbar via OfficeKB.com]

=IF(A1>0,(ROUND(A1/7,0)),"")

This formula will not display a 0 if A1 is empty so you can copy and paste
down


If you want to not include a formula at at you can use a macro.

If you want you can send me an example sheet and I will send you a macro
example of how else to fix it

[email protected]

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

[Sean]

Thank you guys for your help! It works!!

Sean