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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fishboy

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

Active Member
Joined
May 5, 2015
Messages
257
Office Version
  1. 365
Platform
  1. Windows
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>
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,424
Messages
5,837,157
Members
430,479
Latest member
mamush200

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