Question description:
This user has given permission to use the problem statement for this
blog.
How do you get excel to ignore a formula until cell contains a value
i.e.(if B1 is 1000, don't subtract C1 from B1 for a sum in C2 (resulting in -1000), until a value is first put in C1. ANOTHER EXAMPLE Don't add the sum of D5 and D6 in D7 if D5 and D6 are blank, preventing D7 (and E7 and G7...) from showing up as 0.00 ).
I would like totals to remain blank and not the whole row showing 0.00 until a number is placed to complete the formula
Solved by V. J. in 28 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
12/02/2018 - 02:30
Hello!
Excelchat Expert
12/02/2018 - 02:30
Welcome to Excel chat.
Excelchat Expert
12/02/2018 - 02:30
How may I help you?
User
12/02/2018 - 02:30
hello
User
12/02/2018 - 02:31
can you see my question?
User
12/02/2018 - 02:32
How do you get excel to ignore a formula until cell contains a value
i.e.(if B1 is 1000, don't subtract C1 from B1 for a sum in C2 (resulting in -1000), until a value is first put in C1. ANOTHER EXAMPLE Don't add the sum of D5 and D6 in D7 if D5 and D6 are blank, preventing D7 (and E7 and G7...) from showing up as 0.00 ).
I would like totals to remain blank and not the whole row showing 0.00 until a number is placed to complete the formula
Excelchat Expert
12/02/2018 - 02:32
Yes. I have seen that. I understand that you want to keep a cell blank until, a value has been put in required cells . And, in other cell, if a cell contains a particular value, it should not apply the formula.
Excelchat Expert
12/02/2018 - 02:32
This is doable using IF statements.
Excelchat Expert
12/02/2018 - 02:32
Let's work our way through some examples.
Excelchat Expert
12/02/2018 - 02:33
I have entered few values in B1, C1 and C2
Excelchat Expert
12/02/2018 - 02:34
This is your current scenario 1.
Excelchat Expert
12/02/2018 - 02:34
Am I right?
User
12/02/2018 - 02:35
yes. can i add to it?
Excelchat Expert
12/02/2018 - 02:35
Yes, please.
User
12/02/2018 - 02:36
im using the formula =IMSUB(E14,D14)
User
12/02/2018 - 02:36
[number dont apply here obviously]
User
12/02/2018 - 02:37
it does it dont the whoel row though
Excelchat Expert
12/02/2018 - 02:37
No.
User
12/02/2018 - 02:38
so for d2 it then tries e1-d1 which is 0-1500=-1500
Excelchat Expert
12/02/2018 - 02:38
Ok.
Excelchat Expert
12/02/2018 - 02:39
So you want to check if E1 is blank, then formula should not apply. And if E1 has some number only then formula should apply.
User
12/02/2018 - 02:39
and the for e2 it continues f1 (blank which it sees as 0)-e2 which is -1500= -1500 down the whole row
User
12/02/2018 - 02:39
yes
User
12/02/2018 - 02:40
if e1 is blank d1 also should be
Excelchat Expert
12/02/2018 - 02:40
Ok. Let's put some data and find the right formula.
Excelchat Expert
12/02/2018 - 02:40
Where you want to get E1-D1?
Excelchat Expert
12/02/2018 - 02:40
in D2?
User
12/02/2018 - 02:40
oh sorry *d2
User
12/02/2018 - 02:40
yes
Excelchat Expert
12/02/2018 - 02:41
Ok. Easy one. Please see the D2 formula.
Excelchat Expert
12/02/2018 - 02:42
I have written a formula in D2, which makes sure that if there is a number in E1, only then D2 is calculated. If E1 is blank or has other text, then D2 is blank.
Excelchat Expert
12/02/2018 - 02:42
I hope that answers your question.
Excelchat Expert
12/02/2018 - 02:42
Please verify.
User
12/02/2018 - 02:43
is there a way to apply that all the way down the row quickly, or do i manually have to change the cell numbers each row?
User
12/02/2018 - 02:43
and yes that works perfectly thankyou
Excelchat Expert
12/02/2018 - 02:43
You can simply drag down the formula till the cell you want.
User
12/02/2018 - 02:46
so for the one in 8-10
User
12/02/2018 - 02:47
would it be for E10 =IF(ISNUMBER(E8), E8+E9, "")
Excelchat Expert
12/02/2018 - 02:47
Yes.
Excelchat Expert
12/02/2018 - 02:48
But that is a different formula than what I have written before.
Excelchat Expert
12/02/2018 - 02:48
May I know what you want to get done? I may help you better then.
User
12/02/2018 - 02:49
add the totals of mastercard and visa in row 10
Excelchat Expert
12/02/2018 - 02:49
Yes, then you are right.
Excelchat Expert
12/02/2018 - 02:49
If E8 is a number, only then it will add E8+E9
Excelchat Expert
12/02/2018 - 02:51
Does that answer your questions?
User
12/02/2018 - 02:51
awesome
User
12/02/2018 - 02:51
you have been so helpful
Excelchat Expert
12/02/2018 - 02:52
I am glad that I could help you.
Excelchat Expert
12/02/2018 - 02:52
Hope to see you here again soon.
Excelchat Expert
12/02/2018 - 02:52
If you like, I can explain the solution to you.
User
12/02/2018 - 02:52
i search google for an hour and a hour and only can up with a birthday senerio that i couldnt implete into this case
User
12/02/2018 - 02:52
sure
Excelchat Expert
12/02/2018 - 02:52
Ah, I understand, some times Google is frustrating.
Excelchat Expert
12/02/2018 - 02:53
So, in your case, it was a case of checking a condition.
Excelchat Expert
12/02/2018 - 02:53
Excel supports condition checking by an IF function.
Excelchat Expert
12/02/2018 - 02:54
IF has three parts, First is the condition which needs to be checked, Second - Result if condition results true, Third (optional) - Result if condition results in false.
Excelchat Expert
12/02/2018 - 02:55
Here I used ISNUMBER(E1) to check if the cell has number in it. That became the condition which I wanted to check for. Then I told Excel, what to be used when condition results in TRUE or FALSE.
Excelchat Expert
12/02/2018 - 02:55
I hope that answers your question. If you are satisfied, you can close this session.
Excelchat Expert
12/02/2018 - 02:56
Have a nice day ahead. Bye!
User
12/02/2018 - 02:56
i only had found =SUMIF(range,criteria,sum_range)
User
12/02/2018 - 02:56
i wrote many veriations of =SUMIF(E8,"<>",[E8:E9]) but none worked
Excelchat Expert
12/02/2018 - 02:56
Ok. But that's not the right way to do in your case.
User
12/02/2018 - 02:57
your formula with isnumber make much more sense
User
12/02/2018 - 02:57
thank you so much for your time
Excelchat Expert
12/02/2018 - 02:57
I understand. Sometimes articles doesn't mention clearly in what scenario they can be used. I hope I was able to explain it clearly so you can use it with more ease in future.
User
12/02/2018 - 02:57
have a great night/ day if you are on the other side of the world haha
User
12/02/2018 - 02:58
you did
User
12/02/2018 - 02:58
thanks again
Excelchat Expert
12/02/2018 - 02:58
ha ha :). The whole world is a family.
User
12/02/2018 - 02:58
bye
Excelchat Expert
12/02/2018 - 02:58
Bye!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
