Solved one cell subtracted from another, need an answer blank cell

November 6, 2016 at 10:25:40
Specs: Windows 10
This is probably a simple question, but I just don't remember how to to it.

I have cell C2-B3. Simple yes, but I want the response in C3. If the answer is yes, I want the answer in C3, but if the answer is no, then I want C3 to stay blank.
C2= 200, B3=50, C3=150
C2=200, B3=blank, C= shows a blank cell.

Currently it is showing me what is in C2.

This is Excel 2016


See More: one cell subtracted from another, need an answer blank cell

Report •

#1
November 6, 2016 at 11:01:37
If the answer is yes, I want the answer in C3, but if the answer is no, then I want C3 to stay blank.

I see no indication of "Yes" or "No"
so I'm not completely sure what it is your looking for
but try this and see how it works:

=IF(ISBLANK(B3),"",C2-B3)

or

=IF(B3="","",C2-B3)

MIKE

http://www.skeptic.com/


Report •

#2
November 6, 2016 at 11:32:01
mmcconaghy, thank you for the quick response. It didn't work, I got the #value.
What I have is a small money calculator format.
Let's you money for a clearer picture.
C2=1000-B3=50, C3=1050
C2=1000-B3=Blank cell, C3= Blank cell. Currently, in this case, C3 is reading 1050. This is shown for the next 25 lines. I would like to just have blank cells for the next 25 lines.

I hope this clears this up a little.


Report •

#3
November 6, 2016 at 12:31:34
✔ Best Answer
With your data like:
          A          B         C
1)
2)                            1000
3)                  50       =IF(ISBLANK(B3),"",C2-B3)

If you enter the formula I posted into cell C3
if cell B3 is Blamk then cell C3 will be blank.
If cell B3 is 50 then cell C3 will be 950

I have no idea why you would get a #VALUE error, unless there is more going on then what you have shown.

This is shown for the next 25 lines. I would like to just have blank cells for the next 25 lines.

You will have to expand on your explanation.
I have no idea what your talking about.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
November 6, 2016 at 12:46:12
mmcconaghy, thank you once again.

I don't know why it didn't work before, but I copied, pasted, changed the letters as needed and my whole sheet now works great.

Thank you again.


Report •

#5
November 7, 2016 at 06:09:02
@ccmmonaghy:

Can you explain how that c3 entry works - please?

I tried something very similar and couldn't get it to work... Possibly (very likely) my understanding of it or etc. isn't up to much...


Report •

#6
November 7, 2016 at 06:54:58
re: "Can you explain how that c3 entry works - please?"

He already did:

=IF(ISBLANK(B3),"",C2-B3)

"if cell B3 is Blank then cell C3 will be blank.
If cell B3 is 50 then cell C3 will be 950

ISBLANK(C3) checks to see if C3 "Is Blank".

If C3 is blank, then the ISBLANK function is TRUE, therefore the IF is TRUE and the value_if_true argument is returned. "" means return a blank cell.

If C3 is not blank, then the ISBLANK is FALSE, therefore the IF is FALSE and the value_if_false argument is returned, C2-B3.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
November 7, 2016 at 06:58:33
There are two parts to the function, the IF() and the ISBLANK()

The ISBLANK(), as it name implies, simply determines if there is an entry in a particular cell and will return TRUE or FALSE.

So ISBLANK(B3) is testing to see if there is anything in cell B3.

The IF() function has a syntax of:

=IF ( Logical_test , [Value_if_true] , [Value_if_false] )

Note that each section is separated by a Comma.

In the formula, the Logical_test is the ISBLANK()
If the ISBLANK() returns TRUE, the cell is blank, then the Value_if_true is used.
If the ISBLANK() returns FALSE, the cell is NOT blank, then the Value_if_false is used.

In the IF() the Logical_test can be a function, as you see,
and the Value_if_true and the Value_if_false can also be functions.

So in the formula, =IF(ISBLANK(B3),"",C2-B3)
when the Logical_test returns TRUE,
the Value_if_true is a null string, denoted by two double quote marks, IE ""
When the Logical_test returns FALSE,
the Value_if_false is the calculation C2-B3

OK?

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#8
November 7, 2016 at 11:30:35
Many thanks to you both. It would appear the sequence of "events" is important, in that the test to see if the cell is blank must precede the calculation if it isn't.

As I understand it for the above situation or any similar; one must decide on an order of events (in a logical sequence); and then if the first fails, and the second is applied, and if that fails then the third (if applicable) and so?

My knowledge of Excel is rudimentary to say the least...; although I have managed (with a wee bit of help form former colleague at work) to get a formula for my book keeping that works and is logical to me as to how it does it.

I think it helps (obviously) knowing what symbols etc. are used to denote various states/condition - let alone the correct syntax...

Can you chaps recommend a decent book on Excel; not one that costs the earth - but certainly one that would help me get more across it all?

I am now a poor almost penniless retiree so have to watch the pennies. Sadly I had to sell the Bentley as the ashtray was full of sweet papers, and I couldn't afford to have it serviced anymore and get them cleaned out...

Again muchos gracias amigos...


Report •

#9
November 7, 2016 at 11:44:42
Trevor,. Check this site out for helpful info. Ihttp://excelribbon.tips.net

Report •

#10
November 7, 2016 at 12:21:05
re: Many thanks to you both. It would appear the sequence of "events" is important, in that the test to see if the cell is blank must precede the calculation if it isn't.

Yes and No. Yes, the sequence is important, but No, in that the formula could (sort of) be written in reverse:

=IF(NOT(ISBLANK(B3)),C2-B3,"")

By adding the NOT function, you would be checking for a cell that isn't blank as opposed to checking for one that is. :-)

(A waste of keystrokes, but it makes the point)

As I understand it for the above situation or any similar; one must decide on an order of events (in a logical sequence); and then if the first fails, and the second is applied, and if that fails then the third (if applicable) and so?

That is the more accurate way of saying it.

It also explains why some folks get their Nested IF's messed up by not putting their logical_tests in the right order. The following formula will never return No, even for a value greater than 100, because 100 is greater than 1, so the first logical_test will be TRUE for any number greater than 0, including 101. All IF functions stop at the first logical_test that is TRUE. In other words, an IF doesn't evaluate all the logical_tests and then decide on which one is "more TRUE" than any other.

=IF(A1>0,"Yes",IF(A1>100,"No","")) <--- Bad!

=IF(A1>100,"No",IF(A1>0,"Yes","")) <--- OK!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#11
November 7, 2016 at 13:18:44
Here are three sites that you should find useful:

http://chandoo.org/wp/welcome/

http://www.excel-easy.com/

https://www.hscripts.com/tutorials/...

If you every need assistance, just post your questions.

MIKE

http://www.skeptic.com/


Report •

#12
November 7, 2016 at 14:55:01
Again - many thanks for the the tutorials - muchly appreciated...; likewise the links...

Report •

#13
November 7, 2016 at 15:36:47
Just had a very brief look at the info/tutorials etc in the links in #9 and #11 - "very" useful, very helpful... I may yet get to be able to add 'n subtract - and even multiply/divide (and conquer excel) in due course...

Again "muchõs graçias amigos"...


Report •

#14
November 9, 2016 at 15:09:54
Chaps: I have posted a CFH - (Call for Help) re' a very basic/simple... formula here:

http://www.computing.net/answers/of...

Perhaps you can have a look-see and help me understand the intricasy of Excel a wee bit more.. (please).

"Takk" - en-avanec...


Report •

Ask Question