# Removing rows containing zeros

• 07-05-2012, 03:12 AM
Semusal
Removing rows containing zeros
Hello Fellows,

Couple of days ago, some fellows at Excel forum helped me to find out maxima of a sine wave. The method also somehow included points that were close to maxima. So I used IF condition to remove such erroneous points (see attachment column D, E and F). I tried to use this data further for my analysis but apparently I cannot do it until I remove all the rows with zeros in it like row # 6, 7 and 8. Please suggest some procedure to carry out this task.

Secondly, I have plotted voltage maxima vs. time steps. I want a plot without blue curve (time) and x-axis with time stamps 0.0094, 0.0442 and 0.0788 instead of 1, 2 and 3.

Third, If I want to hide coulmns D, E and F using a 'plus' sign to expand or hide these columns, how I can do it?

Any help and suggestion will be much appreciated, thanks in advance!! :)
• 07-05-2012, 03:14 AM
zbor
Re: Removing rows containing zeros
And attachment :)
• 07-05-2012, 03:17 AM
Semusal
Re: Removing rows containing zeros
Attachment 166105

Here you go! :)
• 07-05-2012, 03:24 AM
zbor
Re: Removing rows containing zeros
You can filter out 0 values?
• 07-05-2012, 03:34 AM
Semusal
Re: Removing rows containing zeros
Ahhh yess thanks that can work I guess! But how did you put the expand hide button on the top?
• 07-05-2012, 03:39 AM
zbor
Re: Removing rows containing zeros
Select columns you want to group (D, E, F)
Go to Data -> Group
• 07-05-2012, 03:44 AM
Semusal
Re: Removing rows containing zeros
Thanks man, any idea how to change x axis from 1, 2, 3 to actual time of sine wave?
• 07-05-2012, 03:49 AM
zbor
Re: Removing rows containing zeros
Right click on X axis values -> Select data

And for Horizontal (Category) Axis Labels click Edit and select data in B column.
Is that it?
• 07-05-2012, 04:03 AM
Semusal
Re: Removing rows containing zeros
Yes!! :cool:

One more thing if you can help with... I just checked using the results from filter. The other columns where I want to use the data are still using the data of some rows which were filtered out. Like in the attached file G9 is using H9 and H8 in the formula whereas I want to make it H9 and H5 (the rows in between have been filtered and there data is useless).

Can you suggest some function which can copy the results of the filter in another columns OR change the formulas as per the results of the filter?
• 07-05-2012, 04:28 AM
zbor
Re: Removing rows containing zeros
I've got some, but I don't know is it that:

Instead of your formula where it look H9-H8 and B9-B8 I put to use H9 and B9 and last value where D column is not 0.

Formula:
=IF(D9=0;NA();2*PI()*((H9-LOOKUP(2;1/(D\$3:D8<>0);H\$3:H8))/(B9-LOOKUP(2;1/(D\$3:D8<>0);B\$3:B8)))*1520)
• 07-05-2012, 05:50 AM
Semusal
Re: Removing rows containing zeros
Thanks its working fine! :)