Tech Support Guy banner
Status
Not open for further replies.

Combining Excel formulas into 1 cell

722 views 1 reply 2 participants last post by  draceplace 
#1 ·
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
 

Attachments

See less See more
#2 ·
You can force the Text to integer by changing the formula like this. =IF(INT(D3)>=5,1,0)

That would get you out of comparing to individual values.

BTW, Great question presentation! Welcome to TSG.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top