# Problem with IF statement

#### phil133

##### Active Member
Hi. I will make an example in order to understand what I'm trying to do.

 5 blank 8 blank 1 blank -4 1 blank blank

<tbody>
</tbody>
I'm trying to make a formula in column b so that when the cell in column A is blank the previous cell in column B to give me blank. IF(B5="",1,""). The problem is that this formula will give me 1 in B5 also which I don't want.

(This formula IF(ROWS(J5:J\$5)=COUNT(C:C)-1,\$Z\$8,"") might help but I don't understand what it is doing.)

Thanks for any help!

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hi. I will make an example in order to understand what I'm trying to do.

 5 blank 8 blank 1 blank -4 1 blank blank

<tbody>
</tbody>
I'm trying to make a formula in column b so that when the cell in column A is blank the previous cell in column B to give me blank. IF(B5="",1,""). The problem is that this formula will give me 1 in B5 also which I don't want.

(This formula IF(ROWS(J5:J\$5)=COUNT(C:C)-1,\$Z\$8,"") might help but I don't understand what it is doing.)

Thanks for any help!
Hi phil133, with regards to IF(B5="",1,"") this is what your formula is basically saying:

IF B5 is blank, put a 1 here, otherwise put a blank.

Based on your description and the example data above, there is not enough information to go on. So, lets try and clarify a few things:

1. You want the formulas to go down through column B, correct?
2. If the cell from column A is blank, you want the adjacent cell from column B to be blank, correct?
3. What should happen in column B if the cell in column A isn't blank?

As it stands the formula needed in column B would be IF(A1="","","Whatever your false statement should read")

Last edited:
Can you please explain better? Maybe creating a worksheet here?

With your explanation I understood that in column A you have multiple values. If Column A has a blank cell you want to copy the above cell from column B. Are you sure you want to do this? If you have this formula in column B and column A is blank you will basically copy the formula...

I use something similar but... sometimes I have merged cell is column A. To use pivot table I have to unmerge, then I have empty cells in column A so, I insert a new column and create the formula below in B2 and copy/paste to the end: =IF(A2<>"",A2,B1). This requires to have A2 different from "" (not blank). Then I copy/paste values to column A and delete column B.

 Column A Coulmn B User 1 User 1 User 2 User 2 User 3 User 3 User 4 User 4 User 5 User 5 User 5 User 7 User 7 User 8 User 8 User 9 User 9 User 9 User 9 User 9 User 13 User 13 User 14 User 14

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

Thanks for your answers but I fixed it. When a cell in column A doesn't have a value it will not have a value again. So because the formula in column B has already other if statements I used column K to give me 1 when the next cell in column A is blank with formula
 IF(ROWS(K5:K\$5)=COUNT(C:C)-1,IF(A6="",1),"") and then I added to formula in column B if k5=1,1,"". Thanks again!

<tbody>
</tbody>

Replies
3
Views
255
Replies
7
Views
176
Replies
5
Views
538
Replies
1
Views
177
Replies
13
Views
232

1,217,440
Messages
6,136,627
Members
450,022
Latest member
Joel1122331

### 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.

### Which adblocker are you using?

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

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