Which cells is not true

[Remburg]

The spreadsheet contains information about articles and prizes which has been set up with =if functions. What I am looking for now is if there is a possibility to create a table where excel shows me which of the cells is not true in relationship to the aforementioned if function. Or since I am working with only positive numbers if a formula could tell me what cells contains a negative number?

 

[Roderick_E]

=if(a2<0) would return TRUE if negative number

 

[Remburg]

Yeah, i might have been unclear. I have =if(and()) functions that checks if all of the sum rows are positive. What I am looking for is if a formula could produce a new table where the output shows what cells made the if statement false.

 

[Remburg]

The point is that I want the output to show a reference to the specific cell(s).

 

[shg]

One way:

Row\Col	A​	B​	C​
1​	Input​	Cells < 0​
2​	0​	$A$8	B2:B8: {=ADDRESS(SMALL(IF($A$2:$A$19 < 0, ROW($A$2:$A$19)), {1;2;3;4;5}), COLUMN(A:A))}
3​	6​	$A$10
4​	7​	$A$13
5​	8​	$A$14
6​	4​	$A$16
7​	3​	#N/A​
8​	(2)​	#N/A​
9​	9​
10​	(1)​
11​	3​
12​	8​
13​	(1)​
14​	(5)​
15​	4​
16​	(1)​
17​	7​
18​	1​
19​	(2)​

 

[Roderick_E]

lost in translation somewhere , maybe: =ADDRESS(ROW(),COLUMN())