Excel in OS with different languages

[RonOliver]

Hi all!

KInd of a difficult question here: In English, we use points to mark decimals (6.5), whereas we use commas in Spanish (6,5). I have a problem, as I have created a conditional formatting rule that turns a cell red (let's assume it's A1) if its value is higher than the value in another cell (let's make it C1). The problem is C1 has data validation with a list of two values: 6 and 6.5. If I use Excel with a computer in Spanish, that will mess up the conditional formatting formula, as 6.5 is not a decimal number in any way. My question is: Is there a way for Excel to identify 6.5 as a six point five in English and as a 6,5 (six comma five) in Spanish? I'd like for my worksheet to work regardless of the language my OS is using. Thanks!

 

[RonOliver]

Quick note: I know I can use a helper column and I'd sort out the problem easily, but I wonder if there is a solution to this where I don't need to use a helper column.

 

[Tetra201]

Maybe use a data validation list of 6,6 1/2 or +60/10,+65/10 (untested ).

 

[RonOliver]

I tried it but I couldn't get it to work. Could be that I'm doing something horribly wrong.

 

[Tetra201]

Now I have tested the suggested solution, and it does work on my computer.

What do you see in the Formula Bar when you select 6 1/2 (note the space between 6 and 1) from the drop-down list in cell C1? -- I see 6.5 under the decimal dot system setting and 6,5 under the decimal comma setting.

 

[RonOliver]

Oh, yeah, I do see that in the formula bar, but I see 6 1/2 in the drop-down list. I could tell the final user that 6 1/2 is 6.5, but it's not the best-looking solution. If there was a way for me to be able to write 6,6 1/2 when setting data validation, and see "6" and "6.5" (or "6,5" in Spanish OS) as selectable items, I'd be so glad. Otherwise, I'll just go with the helper column thing. Looks a bit cleaner.

 

[Tetra201]

Just for fun -- it seems to work.
Data validation list in cell C1: 6,6.5
CF formula in cell A1: =$A$1>--SUBSTITUTE($C$1,".",MID(TEXT(1.1,"@"),2,1))