Compare values: indication of greater/equal values, warning if ONE cell is empty

[simontop]

Long thread title but I didn't know how to put it otherwise.
I need to compare values in two columns.
The results yielded should look like this:

-Value of C if the values are identical (empty or value present)
-"C!" or "Z!" if column C or Z is empty but the other contains a value
-"C" or "Z" if value in column C or Z is greater, respectively

I can do this easily by using IF formulas but was wondering whether there is an easier way.

This is what I am using right now:

=IF(C1=Z1;C1;IF(C1>Z1;IF(Z1="";"Z!";"Z");IF(C1="";"C!";"C")))

I need to do this as simply as possible to allow coworkers to understand the formula and adapt it for their departments.

 

[energman58]

I think your formula is wrong - it should look like this I think:

=IF(C1=Z1;C1;IF(C1>Z1;IF(Z1="";"Z!";"C");IF(C1="";"C!";"Z")))

This returns the value of C if they are equal or both blank
it reurns C! if only C is empty
it returns Z! if only Z is empty
it returns C if both have values and C is larger
it returns Z if both have values and Z is larger

I dont think there is a simpler way of doing this

 

[sureshiyer]

It is still giving Error with your formulas i am trying to compare 3 column sample data as given bellow

RMS ID from Finance RMS ID from HR RMS ID from SCM
42866 42866 39319

i would like tell me which is vaild or not vaild

Regards
Suresh

PS send me email if need any clarification to suresh_ir37@yahoo.com