Create Formula to Compare two results

Giganews Newsgroups
Subject: Create Formula to Compare two results
Posted by:  Stu (stu…@totalise.co.uk)
Date: 28 Oct 2003

I want to compare two cells containing scores (2-1 or 1-4 etc). If
both scores are the same I want the result of the comparison to be 2
but if the scores are wrong but the result is correct then I want to
return 1 otherwise the cell should be blank. I've got so far with it
but only by calculating the results for each individual score and
inserting them into other cells using this formula -

=IF(ISBLANK($C8),"",IF(ISBLANK(E8),"",IF(LEFT(E8,1)>RIGHT(E8,1),"W",
IF(LEFT(E8,1)<RIGHT(E8,1),"L",IF(LEFT(E8,1)=RIGHT(E8,1),"D")))))

I then use the following formula to check the actual scores and then
compare reults if the actual scores do not match -

=IF(ISBLANK($C8),"",IF(ISBLANK(F8),"",IF((AND((AND((LEFT($C8,1))=
(LEFT(E8,1)))=TRUE),(AND((RIGHT($C8,1))=(RIGHT(E8,1)))=TRUE))),2,
IF($D8=F8,1,0))))

This works but because I'm using other cells it seems to be a bit
messy if the scores have to be cleared as it's quite easy for someone
to delete the frmulae too. Is it possible to combine these into one
formula and do away with the extra cells.

TIA

Stu

Replies