Create Formula to Compare two results

[Stu]

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

 

[Dave Peterson]

I'm not sure if you're ready to redesign your worksheet, but I think I'd put the
each score in separate columns: 2, 1, 1, 4 (would be 4 columns--not just 2).

Then I think your formulas may be easier to work with--especially when one team
hits double digits!

Either way, you can lock the cells that have formulas and unlock the cells that
should be touched--format|Cells|Protection tab.

Then when you protect the worksheet (Tools|protection|Protect sheet), those
formulas will be safe--along with the headers/descriptions/etc that shouldn't be
changed.