
Subtracting Column B From A, How To Leave Third Column Blank If No Value In Column B?


Search Excel Forum Posts, Tutorials, Macros, Tips, and More
Subtracting Column B From A, How To Leave Third Column Blank If No Value In Column B?  Excel

View Answers


Hello  I have what is proabably a basic question, but I could not find the answer. I have a spreadsheet with three columns (A,B,C). I want the third column C to be column A  B (A minus B) for each row, but only if there is a value in column B. If there is no value in column B, then I want that row in Column C to just stay blank. Is this possible? Thank you.
Similar Excel Video Tutorials
Extract records w Field Not Blank
 See how to use the advanced filter and a TRUE FALSE formula in a column to extract records that contain a non blank in a specified field (column). ...
Text To Column
 Excel! Need to separate first and last name into two cells? Need to take a column of text and break it apart into multiple columns? This is called &qu ...
Helpful Excel Macros
Delete Blank Rows in Excel
 This is a macro which will delete blank rows in excel. This version will delete an entire row if there is a blank cell
Similar Topics
Hi guys,
I need a formula for something like this (all the column leters a examples only)
1. Column A  a column with a Y/N choice
2. Column B  a column with a number (e.g. 10000)
3. Column C  column with a percentage in a positive or negative value (e.g. 40%) the column is formated by percentage.
4. Column D  the column using the formula
the formula I need is:
If "column A" = "Y" (Yes) then "column D" = "column B" minus/plus the value in "column C"
If not leave blank.
The correct answer should be (by the numbers I gave above)
If "column A" = "y", then Column D = 10000 40% = 6000.
If "column C" was a positive value then the answer wil be:
"Column D" = 10000 + 40% = 14000
I hope someone could help me.
i would like to delete the row if the column A, B & C is blanks, just leave the row got column A, B, C & D have word insert.
Column A Column B Column C Column D
1 A101 10 20
blank
blank
2 A102 10 30
blank blank blank 40
blank
blank
blank
3 A102 55 410
I had searched for this, but was unable to find it. I have a need to locate the first available blank column. I receive spreadsheets that are random in the number of columns. They could end on column "X" or the next time I receive it, the last column could be "GA" for example. I haven't seen a blank column inbetween used columns yet. (knock on wood...)
I'm want to insert my calculations on the next available blank column.
Any suggestions?
Robert
Can anyone help, I have tried but my spreadsheet works very slowly and hangs!
Column A ... Here I will be adding email adresses ( must be able to hold at least 1 million)
Column B ... this must automatically Dedupe column A and display in Column B
Column C ... Here I will also be adding adresses (ones that have bounced)
Column D ... Must show same as column B MINUS the ones in column C
Column E ... Here I will also be adding adresses (ones that have unsubscribed & ones that need to be Removed)
Column F ... Must show same as column D MINUS the ones in column E
Column G ... Must only show email addresses that end in .za from column F
Column H ... Must show same as column F MINUS the ones in column G
Totals for each column to show at the top of each column
All columns to be arranged alphabetically
No Blanks
Here's the situation I have:
Column A displays the day of the week (for example, Monday, Tuesday, etc.)
Column C displays either a 1 or is blank
Column E will display a value based on the following condition
If Column A is either Monday, Tuesday, Wednesday or Thursday, AND Column C = 1, then Column E = 10; if Column C is blank, then Column E is blank.
If Column A is either Friday, Saturday or Sunday, AND Column C = 1, then Column E = 20; if Column C is blank, then Column E is blank.
I hope this is clear enough for someone to help.
Thanks in advance
Brian
In column A, I have text, Column B, I have text
I want to compare columns C & E with text in column A
If it matches then in column D, and F, then I would like it to display the text in column b, if not, leave it blank.
Ex.
Column A Column B Column C Column D Column E Column F
Rose 1 Bushes Daisy
Daisy 2 Sunflower Rose
Sunflower 3 Flower Pink
So in column D row 1 & 3 should be blank, but d2 should display the number 3
In column F F1 should be 2, F2 should be 1, and F3 should be blank
i have three columns of data. for each row, data will only appear in one column for example, row 1; column 1 blank, column 2 data, column 3 blank: row 2, column 1 data, column 2 blank, column 3 blank.
i'm using the following formula to resolve the column address, yet it's returning the address of the column with reference from column A, not the column in the range.
Code:
{COLUMN(INDEX($D6:$F6,MATCH(TRUE,$D6:$F6<>0,0)))}
for example, if data is in column D, it returns "4", not 1.
is there a way to change the formula to resolve the address with reference to range? or is it simple a case of the above formula minus 3?
Excel 2003  Column C contains numericals. The data in column C is segregated into Columns D or Column E. Column J contains categorisation like "pro" "vm" "km" "ml" etc. My present request is that if Column E is blank, column J also should be blank. If Column E contains data, Column J should NOT be blank and should contain relative categorisation. The database is fairly large. How do I check now that Column J is not blank when column E has data?
Hi,
I have a column(A) which has figures and blank entries.
In column(B) im using the =whatever is in column(A).
In column(B) it is returning the answer as 0 if column(A) is blank. But i want it to return blank if column(A) has a blank not a 0.
Can you help please.
Thanks
Ru
I have two columns
New
Old
0.06
0.03
what I want to say is that if column 1 is greater than column two put an "X" in column 3, but if COlumn 1 is equal to column 2 then put a "M" in column three otherwise leave blank.
I have tried If(Or statements and it is just not working, what am I doing wrong please?
Thanks in advance.
DEllis
Hello,
I want to compare two columns of data, and where ever there is a match, to subtract.
So in attached spreadsheet, I want to compare column A with column F, and if there is an exact match, then display the difference of coulmn i and d, all else leave blank.
See desired result in column K
How can I do this via sumproduct or vlookup, or any other method?
Thanks
I have several spreadsheets with 12 columns (A:L). I have a master sheet that consolidates the others but decides what to pull in based on whether or not there is a value in column A (therefore it ignores blank rows). Therefore, I need a way to ensure that something always gets placed in column A if there's another value in that row. I tried doing it with a basic If/Then statement but it doesn't really work for me.
Code:
For cell A2: =IF(D2"","MA",IF(E2"","MA",IF(F2"", "MA","")))
For cell A3: =IF(D3"","MA",IF(E3"","MA",IF(F3"", "MA","")))
So on and so forth for column A
I tried this with a macro but had even less luck.
Here's what I need to do:
If there's a value in column D, E, or F, put MA in column A (UNLESS there's already a value in column A, then leave it asis).
I'd like to replicate this for all of column A (excluding A1) but don't want 65336 rows to show up when I try to print it.
Can anyone help?
Ok...so I have tried so many different IF statements my head is about to explode. I am trying to build a nested IF statement however the solution continues to elude me. I have included an couple of scenarios that I would like to create if statements from. In the attachment you will see 2 scenarios. I would like an IF statement for each that accommodates the various possiblities that has been included as well.
If anyone can provide some assistance it would be greatly appreciated.
******** ******************** ************************************************************************>
Microsoft Excel  Book2
___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
D11
=
A
B
C
D
E
1
Scenario*1
*
*
*
*
2
16Oct06
*
*
*
if*the*range*looks*like*this*I*would*like*to*see*the*current*date*minus*the*date*in*column*A*appear*as*days*elapsed*as*t he*answer
3
16Oct06
16Oct06
*
*
if*the*range*looks*like*this*I*would*like*to*see*the*current*date*minus*the*date*in*column*B*appear*as*days*elapsed*as*t he*answer
4
16Oct06
16Oct06
16Oct06
*
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*C*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
5
16Oct06
16Oct06
16Oct06
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*C*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
6
16Oct06
*
*
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*"0"*or*""*as*the*answer
7
16Oct06
*
16Oct06
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*C*minus*the*date*in*column*A*appear*as*days*elapsed* as*the*answer
8
16Oct06
16Oct06
*
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*D*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
9
*
*
*
*
*
10
Scenario*2
*
*
*
*
11
16Oct06
*
*
*
if*the*range*looks*like*this*I*would*like*to*see*the*current*date*minus*the*date*in*column*A*appear*as*days*elapsed*as*t he*answer
12
16Oct06
16Oct06
*
*
if*the*range*looks*like*this*I*would*like*to*see*the*current*date*minus*the*date*in*column*B*appear*as*days*elapsed*as*t he*answer
13
16Oct06
16Oct06
16Oct06
*
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*C*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
14
16Oct06
16Oct06
16Oct06
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*D*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
15
16Oct06
*
*
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*"0"*or*""*as*the*answer
16
16Oct06
*
16Oct06
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*D*minus*the*date*in*column*A*appear*as*days*elapsed* as*the*answer
17
16Oct06
16Oct06
*
06Nov06
if*the*range*looks*like*this*I*would*like*to*see*the*date*in*column*D*minus*the*date*in*column*B*appear*as*days*elapsed* as*the*answer
Sheet1
*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
I have two columns in my spreadsheet. Column A has no blank fields, Column B has some blank fields. I would like to show the text from Column A in the same row of Column B only when Column B is blank.
So, basically I'm after: If column B has text, do nothing. If column B is blank, then list text from Column A.
Right now I've created Column C with this formula =IF(ISBLANK(B1), A1. That's working to get the text from A1 when B1 is blank. What can I do/add to get it pull the text from B1 if there is text there? Or...is there a better approach altogether?
For example in column A I have value number 5 and in Column C I have 7, difference between Col A and Col C is 2 that answer I need in Col B.
But at the same time if I have a value 7 in Column A (greater value then column C )
And value 5 in Column C (lesser value then column A) in this case I want to consider Column C is 5+10=15 so difference between 7 and 15 is 8 which I want to get as a answer in Col B.
It means any time if the C column has a greater value it will minus from the A columns lowest value and show the answer or difference in Column B.
but if the C Columns value is less then the A Column then C Columns value will get added to +10 to any values already in C column (example 2+10 or 5+10)
I think in excel, if( ),> and < formula will work for this but I do not know how to write the formula.
If anybody can help me,
Thanks I Appreciates
I have a project I am working on for work. It involves 3 columns: Column A is the person's name, Column B is the computer name of the user submitted by the user, and Column C is the computer name of the user as provided from a report. Some users left Column B blank so we ran the report in Column C to try to fill in the blank cells. I need to merge columns B and C. I want to merge the columns so that column C is the "master" column and column B only fills in the blank cells of column C.
I have read through about a dozen posts about merging columns but I could not find one where one column is a "master" column that is not overwritten unless there are blank cells.
Any help would be greatly appreciated.
I am trying to divide column J by column I to get the answer to column K. Is there a way to do this and still leave blank the K rows where there is not a number in either column J or I? I am trying to get rid of the message #DIV/0! AND #VALUE!. Any help that you can provide me will be greatly appreciated!! Thank you, Jill
Say I have Rows 1 through x and Columns A through AQ. Row 1 is the name of the columns, so ignore it. I would like to do this:
For Columns A, B, D, E, F, G, H, I: Take Row 2 and copy it to Row 4
For Column C: On row 4, put "LastHalf"
For Columns J through AP: each column equals that column on Row 2 minus that column on Row 3 (for example, 4,J = 2,J  3,J).
Now where this become difficult (for me) is that I want this to repeat for every third row. So Row 4 needs to do this based on the two rows above it, then Row 7 needs to do this for the two rows above it, then Row 10 needs to... you get the drift. Every third row (all of them will be blank) needs to automatically fill in based on the two rows preceding it.
I know this is a lot to ask, but I'm desperate...
Ok, this one is sorta complicated, and I haven't been able to find a good answer. Also, not wanting to use macros, since this is a schedule and will need to be sent out to many people (some with mac office 08).
So I have 3 columns, set up like this:
Time  Name  Total hours
.25  
.5  
1.5  Sally  2.25
1  
.75  
1.25  
1  Joe  4.00
So, I need formulas for that 3rd column (C). It should sum the values of the 1st column (A) on the same row and up to the row below the one where column B is not blank.
i.e. for the cell to the right of Joe (C8), it should add cells A8,A7,A6,A5 and not add A4 or before (or any below), because it has found a non blank cell in B4 where Sally's block ends.
(also, it should return blank if the adjacent B cell (name) is blank, but i can do that part)
Thank you so much!!!
Hello, I'm pretty new to writing excel macros, and I'm sure this is pretty basic and I'm sorry if it is. I have excel 2007.
I have 8 columns (with various info), Column A is blank to start with, and Column G has various text through all the spreadsheet (approx 67 thousand lines). I am looking for a way to Find specific text (for example "abc" in Column G) and if if Column A is blank to place a specific word (to categorize the type of info being referenced in the Column G's text).
For example if Column A is blank and Column G has text that contains within it the words "NFS alert", then I would like to populate Column A with "Network".
I would need to modify this command to search for other various text in Column G to catergorize the record as "Software", "Hardware", etc. using Column A.
Thanks
Frank
The problem is: selecting a depth of blank column using a previously filled column next to it so that the blank column is the same length (depth) as the column next to it in a simple fashion. In the good old days of Lotus 123, if we had a column(A) with consecutive numbers going down let's say 20 rows, we could highlight both the (A) column and the blank (B) column right next to it and then select [end down] and then both columns would be highlighted down to the 20th row. We would then just move our cursor to the blank column(B) and then do what we needed with the highlighted (20 row) blank column. In effect, we used the depth of the numbered column to highlight and get to the bottom (equal position) of the blank column. It worked like a charm. How does one do this in a simple fashion in Excel?
I have 3 columns, I need to take the first letter of column 2 and the remaining letters of column 1 to create column 3's user id.
Column A  LastName
Column B  FirstName
Column C  UserID (Currently Blank)
Example:
ColumnA Column B Column C (Result Column)
  
Anderson Heidi HAnderson
I have two spreadsheets. The first spread sheet has three columns and the second spreadsheet has two columns. I want to take column "A" in the second spreadsheet and compare it to column "A" in the first spreadsheet and if it finds a match then I want it to print what is in column "C" on the first spreadsheet into column "B" on the second spreadsheet.
EX:
spreadsheet #1
Column A Column C
2 3
4 5
6 7
8 9
Spreadsheet #2
Column A Column B
5 blank
10 blank
4 5
8 9
Hi!
I have a question regarding filtering of columns, hopefully someone is able to put me on the right track. I have set up Excel 2007 so that I can click on the dropdown menu in each column to filter them.
However, in my data set there is one blank row separating two set of rows. Now, when I apply the filter on a column, all the rows above the blank row filter correctly, but all the rows below stay unfiltered.
I guess Excel only looks at the consequtive rows, then stops when it hits a blank row. Is there any way of applying the filter beyond a blank row (i.e. the whole column)?
Any advice is higly appreciated.
I am new to the forum but have found some helpful stuff here so far.
I have a project that I am working on and have over 68 thousand records that have all the data in one column that I need to get into multiple columns. The good thing is each record is broken up by a blank row but some records could have 1 line or 6 lines that I need to move to columns. If there is only 1 row then it goes to column 6, 2 rows then they go to column 5 and 6, 3 lines column 4, 5 and 6...etc.
Example:
data
data
data
<blank row>
data
<blank row>
data
data
<blank row>
data
data
data
Desired result:
Column 1 Column 2 Column 3
this didnt come out right. if i need to i can poste the spreadsheet
Thanks,
Russell

