Excel IF function on empty cell range
Solved
lilas208
Posted messages
1
Registration date
Status
Member
Last intervention
-
Aziz Llallouche -
Aziz Llallouche -
Hello everyone,
I'm stuck on something in Excel and I'd be very grateful if you could help me.
Actually, in an Excel table, I have a range of cells from C1 to C6, and in C7 I intend to write the following formula: if the range of cells C1 to C6 is empty, it should write 0 in cell C7; otherwise, write 1.
For example: If I write 2P in cell C2, the cell C7 with the formula will be 1. If I leave all the cells (C1 to C6) empty, C7 should be equal to 0.
So, I tried using the function =IF(ISBLANK(C1),0,1) and it works for cell C1, but as soon as I put the range (C1:C6) instead of (C1), which gives =IF(ISBLANK(C1:C6),0,1), it shows me ERROR.
I hope it's possible in Excel and I'm sure you know how to do it, write to me soon!
Lilas
I'm stuck on something in Excel and I'd be very grateful if you could help me.
Actually, in an Excel table, I have a range of cells from C1 to C6, and in C7 I intend to write the following formula: if the range of cells C1 to C6 is empty, it should write 0 in cell C7; otherwise, write 1.
For example: If I write 2P in cell C2, the cell C7 with the formula will be 1. If I leave all the cells (C1 to C6) empty, C7 should be equal to 0.
So, I tried using the function =IF(ISBLANK(C1),0,1) and it works for cell C1, but as soon as I put the range (C1:C6) instead of (C1), which gives =IF(ISBLANK(C1:C6),0,1), it shows me ERROR.
I hope it's possible in Excel and I'm sure you know how to do it, write to me soon!
Lilas
7 answers
Hello
I thought there might be a formula where I wouldn't have to count the number of lines
You thought well!
By reasoning differently, instead of counting the empties, you count the filled ones:
If you have formulas in the cells, the formula doesn't work because it analyzes the content, not the result at zero or nothing!
It's up to you to decide...
--
Knowledge is the only thing that increases when shared. (Socrates)
I thought there might be a formula where I wouldn't have to count the number of lines
You thought well!
By reasoning differently, instead of counting the empties, you count the filled ones:
=IF(COUNTA(C1:C25)=0,0,1)
If you have formulas in the cells, the formula doesn't work because it analyzes the content, not the result at zero or nothing!
It's up to you to decide...
--
Knowledge is the only thing that increases when shared. (Socrates)
na2s2o3
Posted messages
1
Status
Member
Excel_lent, one had to think of it; it mainly allows us to take into account the fact that Zero is a value, which is different from an empty cell! That's what I was looking for! Thank you.
Aziz Llallouche
Thank you very much
Hello,
I would like to know the formula that will give me this logic:
if cell B1 is empty, A1=0 but if B1 is not empty, A1 returns the exact time of the entry of B1
Thank you for your help
I would like to know the formula that will give me this logic:
if cell B1 is empty, A1=0 but if B1 is not empty, A1 returns the exact time of the entry of B1
Thank you for your help
Hello,
I would like to know the formula that can give me this logic:
If cell B1 is empty, A1=0, but if B1 is not empty, A1 returns the exact time of the entry in B1.
Thank you for your help.
I would like to know the formula that can give me this logic:
If cell B1 is empty, A1=0, but if B1 is not empty, A1 returns the exact time of the entry in B1.
Thank you for your help.
Hello
A simple thing: instead of "estvide", use sum(C1:C6), if the total is equal to 0, then C7 = 0
=IF(Sum(C1:C6)=0,0,1)
--
*** @+ / Stealth ***
*** I love Epicurus! ***
A simple thing: instead of "estvide", use sum(C1:C6), if the total is equal to 0, then C7 = 0
=IF(Sum(C1:C6)=0,0,1)
--
*** @+ / Stealth ***
*** I love Epicurus! ***
Hello,
Here is a formula that checks the number of empty cells in the specified range:
Copy this formula into cell C7.
=IF(COUNTA(C1:C6)=6,0,1)
Translation: If the number of empty cells from C1 to C6 is equal to 6, then the result in C7 will be 0; otherwise, it will be 1.
It should work well, see you later!
Here is a formula that checks the number of empty cells in the specified range:
Copy this formula into cell C7.
=IF(COUNTA(C1:C6)=6,0,1)
Translation: If the number of empty cells from C1 to C6 is equal to 6, then the result in C7 will be 0; otherwise, it will be 1.
It should work well, see you later!
Euhhh, no, if C1 or C2 or ... C6 contains a formula, the result is 1 and not 0, even if that formula returns 0.
Ultimately, we could write: =if(and(c1=0;c2=0;c3=0;c4=0;c5=0;c6=0);0;1) ... but when it's monitoring 45 cells, it's long.
--
*** @+ / Stealth ***
*** I love Epicurus! ***
Ultimately, we could write: =if(and(c1=0;c2=0;c3=0;c4=0;c5=0;c6=0);0;1) ... but when it's monitoring 45 cells, it's long.
--
*** @+ / Stealth ***
*** I love Epicurus! ***
"Euhhh, no, if C1 or C2 or ... C6 contains a formula, the result is 1 and not 0, even if that formula returns 0."
It should return "" and not zero, thanks to a formula like =IF((A6<>"");A6*B6;""), for example, and my formula works very well!
However, the formula with SUM will not work because it only takes numbers into account! If we write text in one of the cells, the sum will remain at zero!
I'll let you test it...
It should return "" and not zero, thanks to a formula like =IF((A6<>"");A6*B6;""), for example, and my formula works very well!
However, the formula with SUM will not work because it only takes numbers into account! If we write text in one of the cells, the sum will remain at zero!
I'll let you test it...
Thank you all, indeed, I thought about Benzebuth's solution, but as the table is quite large and I need to write this formula at least 6 times and the number of empty cells varies each time, I thought there might be a formula where I wouldn't have to count the number of rows. So, in the end, I will do as you said, Benzebuth.
Thanks again, everyone!
Thanks again, everyone!
Hello everyone,
Thanks to Benzebuth who has just unknowingly answered a question that no one has replied to see https://forums.commentcamarche.net/forum/affich-26866469-reconu-comme-superieur-a-zero
Thanks to Benzebuth who has just unknowingly answered a question that no one has replied to see https://forums.commentcamarche.net/forum/affich-26866469-reconu-comme-superieur-a-zero