Excel fonction si sur plage de cellules vide
Solved
lilas208
Posted messages
1
Registration date
Status
Membre
Last intervention
-
Aziz Llallouche -
Aziz Llallouche -
Bonjour à tous,
Voilà je suis bloquée sur un truc sur excel et je vous serais très reconnaissante si vous pourriez m'aider.
En fait, sur un tableau excel, j'ai une plage de cellules c1 à c6, et dans c7 j'ai l'intention d'écrire la formule suivante : si la plage de cellules c1 à c6 est vide, il faut écrire 0 dans la cellule c7, sinon écrire 1.
Par exemple : Si j'écris 2P dans cellule c2, la cellule c7 avec la formule sera 1. Si je ne mets rien dans toutes les cellules (c1 à c6); c7 devra être égale à 0.
Donc, j'ai essayé avec la fonction =si(estvide(c1);0;1) et ça marche pour la cellule c1 mais dès que je mets la plage (c1:c6) au lieu de (c1) ce qui donne =SI(ESTVIDE(c1:c6);0;1) ça me mets ERREUR.
J'espère que c'est possible sur excel et je suis sûre que vous savez comment faire, écrivez-moi vite !
Lilas
Voilà je suis bloquée sur un truc sur excel et je vous serais très reconnaissante si vous pourriez m'aider.
En fait, sur un tableau excel, j'ai une plage de cellules c1 à c6, et dans c7 j'ai l'intention d'écrire la formule suivante : si la plage de cellules c1 à c6 est vide, il faut écrire 0 dans la cellule c7, sinon écrire 1.
Par exemple : Si j'écris 2P dans cellule c2, la cellule c7 avec la formule sera 1. Si je ne mets rien dans toutes les cellules (c1 à c6); c7 devra être égale à 0.
Donc, j'ai essayé avec la fonction =si(estvide(c1);0;1) et ça marche pour la cellule c1 mais dès que je mets la plage (c1:c6) au lieu de (c1) ce qui donne =SI(ESTVIDE(c1:c6);0;1) ça me mets ERREUR.
J'espère que c'est possible sur excel et je suis sûre que vous savez comment faire, écrivez-moi vite !
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