Problem with IF statement

[phil133]

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!

 

[Fishboy]

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")

 

[Menumorut]

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>

 

[phil133]

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>