Background: I have 5 cells: Four contain scores (between 1-10 - using drop-down menus) and the fifth evaluates the 4 scores and if any of the four cells contain a certain value (above 5) this cell reflects a numerical value of 1, only once regardless of how many times the score is above 5. This is to use Pivot Tables.
Additional Issue: there are half-scores in TEXT format, because the client wants the export to MS Word to show a 1.5/2.5 up to 9.5 etc with a DOT, not a comma.
Progress to date: Before it was text, I just used >= than 5 for the formula, but now I have the added issue of the TEXT. I've successfully created a formula for one of the cells, but can't get it correctly combined for all 4 cells. What I have used is this:
=IF(OR(EE4>=5;EE4="5.5";EE4="6.5";EE4="7.5";EE4="8.5";EE4="9.5")*AND(EE4<>"1.5";EE4<>"2.5";EE4<>"3.5";EE4<>"4.5");1;"")
I can't get it to apply to cell BT4, CO4 and DJ4 regardless what combination I have used to duplicate the formula to include all the cells.
Attached is an example of what I'm trying to do, in a simpler file example.
Can you assist?
Thank you
Additional Issue: there are half-scores in TEXT format, because the client wants the export to MS Word to show a 1.5/2.5 up to 9.5 etc with a DOT, not a comma.
Progress to date: Before it was text, I just used >= than 5 for the formula, but now I have the added issue of the TEXT. I've successfully created a formula for one of the cells, but can't get it correctly combined for all 4 cells. What I have used is this:
=IF(OR(EE4>=5;EE4="5.5";EE4="6.5";EE4="7.5";EE4="8.5";EE4="9.5")*AND(EE4<>"1.5";EE4<>"2.5";EE4<>"3.5";EE4<>"4.5");1;"")
I can't get it to apply to cell BT4, CO4 and DJ4 regardless what combination I have used to duplicate the formula to include all the cells.
Attached is an example of what I'm trying to do, in a simpler file example.
Can you assist?
Thank you