I have a spread sheet with 5 columns
+----+----+----+----+-------------+ | C1 | C2 | C3 | C4 | C-Aggrogate | +----+----+----+----+-------------+ |YES | | | | YES | | |YES | | | YES | | | |YES | | YES | | | | |YES | YES | |YES |YES | | | YES | |YES | |NO | | MAYBE | |YES |YES |NO | | MAYBE | |YES |YES |NO |NO | MAYBE | |NO | | | | NO | | |NO | | | NO | | | |NO | | NO | | | | |NO | NO | +----+----+----+----+-------------+
Columns C1 - C4 are static data from other sources. C-Aggrogate is intended to aggrogate the other columns and highlight when there is a possible discrepency.
The rules for a given row are:
Where would I begin to create the correct formula for C-Aggrogate? YES, NO, MAYBE are jusst sample values, they could be any human-readable values.
Not pretty, but seems to meet your requirement:
=IF(AND(COUNTIF(A1:D1,"YES")>0,COUNTIF(A1:D1,"NO")=0),"YES",
IF(AND(COUNTIF(A1:D1,"NO")>0,COUNTIF(A1:D1,"YES")=0),"NO",
IF(AND(COUNTIF(A1:D1,"YES")=0,COUNTIF(A1:D1,"NO")=0),"","MAYBE")))