So let's say the above table is an excel document. I wanna write a function in "A1" that would return "True" or "False" based on what is in column "B", there should be only number 1-s in there, except for the header ofcourse, which shouldn't count, so how do i do that?

Checking for every single cell (next to it with IF function) is not difficult, but i wanna know if there are only number 1-s in the ENTIRE B column or not, i don't need to know anything else, or where (the position), i only need to know if there is one or not.

Meaning, if i were to change a number in cell "B 25 500" from 1 to 2 (for example), the "A1" cell should say "False", if there are only 1-s, it should say "True".

Something like this:

IF(@B:B=1;"True";"False")

(should be "B2:B", like start from B2 and check all the columns in B afterwards)

But that function doesn't work obviously, it only checks for B1 cell and ignores the others.

Is there an easy way to solve this, is it even possible to do with only excel functions (prefered) or do Macros and VBA have to be used for this?

Any help would be appriciated :)

Greetings and thanks

CodePudding user response：

If you make a table out of the two columns you could use: =[@header]=1

If you want the result in one go in one cell, you could use =COUNTIFS(B:B,"<>1",B:B,"<>")=1 or =SUM(UNIQUE(FILTER(B:B,ROW(B:B)<>1)))=1

CodePudding user response：

Solution (thanks to @P.b & @JvdV):

=COUNTIFS(B:B;"<>1";B:B;"<>")=1

=1 - because of the Header (Header increases the count by 1)

=0 - if you use no Header

Explanation on what the code does (by P.b):

Edit:

If you should check if all numeric values are equal then use AVERAGE or VAR:

=AVERAGE(B:B)=1

or

=VAR(B:B)=0

for AVERAGE you should change number to check accordingly if that is other than 1.