Deleting specified number of cells using If

**jy2009** · 03-29-2009, 02:23 PM

Hi,

I have a column with "1" and "0". I'd like to have something like, if the value in a cell within the column is "0", then delete the 4 cells previous to the cell that contained "0" within the row. Instead of deleting, the values can also be replaced with anything e.g., zero or n/a etc.

Thank you.

**Paul** · 03-29-2009, 03:12 PM

Hi JY,

Do the previous four cells in that row have any impact on the value in the fifth column? For example, let's say your values are in columns A through E. Does the formula in column E included any of the cells from that row in columns A through D? If so, using a formula to change columns A:D to 0/NA/blank/etc. would create a circular reference and I'd recommend a macro instead.

If, however, columns A:D of that row don't impact the value in column E, then you could change the formulas for columns A:D to include an IF statement that tests the value in column E, for example:

=IF(E1=0,0,G1*0.5)

This says that if cell E1 equals zero, show zero, otherwise do your normal calculation - in this case cell G1 times one-half.

Hope that helps!

**jy2009** · 03-29-2009, 05:08 PM

Hello,

For the column that contains "1" and "0": in order to get the "1" and "0", I have a formula that compares one of the four cells before the cell that will contain "0" or "1" to another cell in the sheet (not part of the four cells before the "1" or "0" cell).

The four cells before contain only numbers, no formulas.

The statement you suggested: =IF(E1=0,0,G1*0.5)
I'm just wondering what G1 is.

The column that contains "1" or "0" for me is AO, and the 4 columns before are AN, AM, AL, AK.

And, I'm wondering where I should paste in the IF statement you suggested.

**Paul** · 03-29-2009, 05:27 PM

You didn't provide any column letters in your original post, so I made up cell G1 as an example.

...For the rest of this post, assume we're talking about AK5:AO5...

Since the "four cells" (AK5:AN5) don't have formulas and are just numbers, we have two options:

1. Use a macro to clear the values in those cells when AO5 is 0; or
2. Use conditional formatting to just hide the values (but they still exist)

In option two, you could select AK5:AN5, click Home ribbon -> Styles section -> Conditional Formatting -> New Rule..., select "Use a formula to determine which cells to format" and then set the formula (in the textbox that appears below) to =$AO5=0 and click the Format button to set the font color for the cell to white.

You can then copy that conditional formatting to all the other rows above and below so that they follow suit.

Let us know if you have more questions..