Hi,
I preparing a French revision spreadsheet and trying to set up the
following, without much success.
In ColA I have English sentences. In Col B I have the correct French
translation of these sentences. (The sentences vary in length)
I want to hide Col B (I can do that!) and then I want to type in ColC
my version of the French translation of Col A.
Here's the part I can't do...
If I get it wrong (i.e. less than 75%), when comparing ColC to ColB, I
want to Excel to higlight the cell in red, if I get at least 75%-99%
of the sentence right I want Excel to highlight the cell in yellow and
if I get the translation 100% correct I want Excel to highlight the
cell in green.
I know it's got something to do with conditional formatting but I
cannot work out the logic to highlight cells based on percentage
correct...can someone help moi!

Thanks for reading and helping

Tony

You need to do have some formula to workout the percentage correct.

Excel cannot do that It is very difficult to workout a percentage of
correctness for a sentance.

But once you have devised a way of working out the %age you put the answer
in a cell and then use the conditional formatting on that cell as a formula.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

Hi,
I preparing a French revision spreadsheet and trying to set up the
following, without much success.
In ColA I have English sentences. In Col B I have the correct French
translation of these sentences. (The sentences vary in length)
I want to hide Col B (I can do that!) and then I want to type in ColC
my version of the French translation of Col A.
Here's the part I can't do...
If I get it wrong (i.e. less than 75%), when comparing ColC to ColB, I
want to Excel to higlight the cell in red, if I get at least 75%-99%
of the sentence right I want Excel to highlight the cell in yellow and
if I get the translation 100% correct I want Excel to highlight the
cell in green.
I know it's got something to do with conditional formatting but I
cannot work out the logic to highlight cells based on percentage
correct...can someone help moi!

Thanks for reading and helping

Tony

On Feb 13, 12:27 pm, Martin Fishlock
wrote:
You need to do have some formula to workout the percentage correct.

Excel cannot do that It is very difficult to workout a percentage of
correctness for a sentance.

But once you have devised a way of working out the %age you put the answer
in a cell and then use the conditional formatting on that cell as a formula.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



" wrote:
Hi,
I preparing a French revision spreadsheet and trying to set up the
following, without much success.
In ColA I have English sentences. In Col B I have the correct French
translation of these sentences. (The sentences vary in length)
I want to hide Col B (I can do that!) and then I want to type in ColC
my version of the French translation of Col A.
Here's the part I can't do...
If I get it wrong (i.e. less than 75%), when comparing ColC to ColB, I
want to Excel to higlight the cell in red, if I get at least 75%-99%
of the sentence right I want Excel to highlight the cell in yellow and
if I get the translation 100% correct I want Excel to highlight the
cell in green.
I know it's got something to do with conditional formatting but I
cannot work out the logic to highlight cells based on percentage
correct...can someone help moi!

Thanks for reading and helping

Tony- Hide quoted text -

- Show quoted text -

Thanks for replying Martin,
The reason that I asked is that I have seen this technique used in
quiz spreadsheets at http://www.quiz-zone.co.uk and I thought I could
extend it to my french revision spreadsheet. I didn't realise that it
was as difficult - looks like they have found a way to work out %

Tony

The excel quizzes are very nice and it would be interesting to see how they
did it.

I know it is done with conditional formatting based on te answer sheet and
what I suspect that that compare letter to letter and give a percentage
correct.

Now with french it is much more difficult because you can use more than one
word to answer and what about graves etc.

But it would still be interesting to see how they did it.

May be I have a try tomorrow.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


" wrote:

On Feb 13, 12:27 pm, Martin Fishlock
wrote:
You need to do have some formula to workout the percentage correct.

Excel cannot do that It is very difficult to workout a percentage of
correctness for a sentance.

But once you have devised a way of working out the %age you put the answer
in a cell and then use the conditional formatting on that cell as a formula.

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.



" wrote:
Hi,
I preparing a French revision spreadsheet and trying to set up the
following, without much success.
In ColA I have English sentences. In Col B I have the correct French
translation of these sentences. (The sentences vary in length)
I want to hide Col B (I can do that!) and then I want to type in ColC
my version of the French translation of Col A.
Here's the part I can't do...
If I get it wrong (i.e. less than 75%), when comparing ColC to ColB, I
want to Excel to higlight the cell in red, if I get at least 75%-99%
of the sentence right I want Excel to highlight the cell in yellow and
if I get the translation 100% correct I want Excel to highlight the
cell in green.
I know it's got something to do with conditional formatting but I
cannot work out the logic to highlight cells based on percentage
correct...can someone help moi!

Thanks for reading and helping

Tony- Hide quoted text -

- Show quoted text -

Thanks for replying Martin,
The reason that I asked is that I have seen this technique used in
quiz spreadsheets at http://www.quiz-zone.co.uk and I thought I could
extend it to my french revision spreadsheet. I didn't realise that it
was as difficult - looks like they have found a way to work out %

Tony