Problem with IF statement

phil133

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
Hi. I will make an example in order to understand what I'm trying to do.


5blank
8blank
1blank
-41
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.


5blank
8blank
1blank
-41
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:
Upvote 0
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 ACoulmn B
User 1User 1
User 2User 2
User 3User 3
User 4User 4
User 5User 5
User 5
User 7User 7
User 8User 8
User 9User 9
User 9
User 9
User 9
User 13User 13
User 14User 14

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top