Problem with IF statement

phil133

Board Regular
Joined
May 5, 2015
Messages
235
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!
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fishboy

Well-known Member
Joined
Feb 13, 2015
Messages
4,261
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:

Menumorut

Board Regular
Joined
Sep 26, 2013
Messages
51
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>
 

phil133

Board Regular
Joined
May 5, 2015
Messages
235
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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
Top