Translation of texts contained in Excel formulae

Question

Dear Community! 
 I need help with the settings of an excel containing the following formulae: 
 
 I have translated the texts in Columns "Probabilidad" and "Impacto en Talgo", replacing them with "High " and "Very high", however, as you can see, the referenced values in the two Columns on the right show an error. 
 Above, you can see the formula, containing the same parameters (high, low, etc.).They are not translated and this is what causes the errors. 
 Any idea on how to make this embedded content visible in Trados and avoid the error? Seems like it is impossible to do it manually. 
 Thanks in advance!

Paul · Answer

You said: 
 Unknown said: I have translated the texts in Columns "Probabilidad" and "Impacto en Talgo", replacing them with "High " and "Very high" 
 How did you do this? Were they extracted text in Trados or you just did this in the Excel? 
 If the text you saw in the cells were there as a result of formatting of the numbers, or a lookup up table for example, then adding in text so they are English could cause an error since the formula could be looking for a number value. Or something like that... you would need to provide more context to get a better response.

Paul · Answer

I didn't really understand this comment: Unknown said: * Establecer acción is in herited from the original file (does not require fixing) But I did manage to get rid of the error... but I have some questions over the result because I could not test exactly what you had since I have an English version and the syntax is a bit different (SI vs IF etc.). But essentially, I translated the values in the Ayuda worksheet only, then replaced this: =IF(H13="","",AVERAGE(IF(H13="5 (Muy alto)", 5, IF(H13="4 (Alto)",4, IF(H13="3 (Medio alto)",3, IF(H13="2 (Medio bajo)",2, IF(H13="1 (Bajo)", 1, IF(H13="0 (Nulo)",0,"--")))))),IF(I13="5 (Muy alto)", 5, IF(I13="4 (Alto)",4, IF(I13="3 (Medio alto)",3, IF(I13="2 (Medio bajo)",2, IF(I13="1 (Bajo)", 1, IF(I13="0 (Nulo)",0,"--")))))), IF(I13="5 (Muy alto)", 5, IF(I13="4 (Alto)",4, IF(I13="3 (Medio alto)",3, IF(I13="2 (Medio bajo)",2, IF(I13="1 (Bajo)", 1, IF(I13="0 (Nulo)",0,"--")))))))) With this: =IF(AND(H13<>"", I13<>"", J13<>""), AVERAGE(IF(H13="5 (Very high)",5,IF(H13="4 (High)",4,IF(H13="3 (Average high)",3,IF(H13="2 (Average low)",2,IF(H13="1 (Low)",1,IF(H13="0 (Null)",0,0)))))), IF(I13="5 (Very high)",5,IF(I13="4 (High)",4,IF(I13="3 (Average high)",3,IF(I13="2 (Average low)",2,IF(I13="1 (Low)",1,IF(I13="0 (Null)",0,0)))))), IF(J13="5 (Very high)",5,IF(J13="4 (High)",4,IF(J13="3 (Average high)",3,IF(J13="2 (Average low)",2,IF(J13="1 (Low)",1,IF(J13="0 (Null)",0,0))))))), "") And I have no error. I also changed this: =IF(J13="","",IF(J13>4,"Establecer acción!",IF(J13=4,"Establecer acción!","--"))) with this: =IF(J13="","",IF(J13>4,"Set action!",IF(J13=4,"Set action!","--"))) To be honest I can't put my finger on exactly why this worked, and I can't check it properly as I think I'm missing data to make the formulae return a realistic result, but at least I was able to use the translated values without an error. So I do think the only stuff you should be translating is on the Auyuda worksheet. Then your problem will be to update the spreadsheet. If the lookups used a range reference instead of static text then the new values would be displayed automatically. The current dropdown stores values like "4 (High)", but Excel treats this as plain text because this is essentially what it is now. If instead, sthe spreadsheet was written to store the numeric value (4, 5, etc.), and used a formula to display the translated version then it would all work automagically :-) Quite a challenge for you this one!!

Trados Studio > 1. Trados Studio

Translation of texts contained in Excel formulae