/ Excel Help for a dummy

This topic has been archived, and won't accept reply postings.
Glenn Sutcliffe - on 15 Apr 2019

Ok be gentle with me, I have created a document in Excel, one column has lots of yes / no answers.

What I want to be able to happen automatically is if the answer to the last 5 questions are all NO in an other cell below I want the words LOW RISK to appear, if the answer to 1 or more of the questions is YES then rather than low risk I want MEDIUM RISK to appear in that cell.  Is this possible?

From endless googling and watching various youtube videos (& losing the will to live) I think it needs to be a macro?  This is slightly above my knowledge level, It appears to be like trying to learn another language overnight.  Until 2 years ago I didnt know 'parentheses' meant brackets, I think I skipped that day at Blaydon Comprehensive School

If there is some kind soul who can help it would be greatly appreciated.  If it helps the yes/no answers are in cells I 50:54 and I want the text to appear in I 56...... As I say be gentle with me, glove puppets may be needed to help the expaination.

elliot.baker - on 15 Apr 2019
In reply to Glenn Sutcliffe:

I don't think you need a macro....

If I'm reading it right then I think you just need something like this, I envisage this going in a column next to your yes/no column and counting the 5 cells above it in the column to the left of it: 

=IF(COUNTIF(C3:C7,"No")>=5,"Low Risk","Medium Risk")

(If column C is where your Yes's and No's are)

So this is has two components: 

The countif counts the number of No's in the 5 cells between C3 and C7, this returns a number between 0 and 5.

Then the If() which the countif is inside, says if the output of the above is greater than or equal to 5 then say "Low Risk" (i.e. they are all No's"; Otherwise (i.e. 1 or more cells that do not say "No") say "Medium Risk".

If you wanted you could put data validation on column C so you could only enter Yes or No, even have a drop down box.

Snyggapa - on 15 Apr 2019
In reply to Glenn Sutcliffe:

=IF(COUNTIF(I50:I54,"NO")=5,"Low Risk","Medium Risk")

usual disclaimer applies - my advice is worth what you pay for it as I wouldn't generally start from here

--edit-- Elliot gives a fuller answer as to why this may be a bad idea, however it will work as long as you are aware of the caveats 

Post edited at 14:41
Glenn Sutcliffe - on 15 Apr 2019
In reply to elliot.baker:

Cheers Elliot, thanks for that, that appears to have worked, the formulas are really not intuitive to me, I would never have worked that out in an hundred years, it has thrown a slight spanner in the works, in that Medium Risk is now alway showing rather than nothing when the 5 cells above are empty, I can work with that or at least I now have a sporting chance of working out how to sort by editing the formula... he says confidently

I had managed to create a drop down box for the answers..... I was pretty chuffed with myself in doing that

Glenn Sutcliffe - on 15 Apr 2019
In reply to Snyggapa:

Cheers Snyggapa, much appreciated

elliot.baker - on 15 Apr 2019
In reply to Glenn Sutcliffe:

congrats on the drop down box!

Re: the empty cells things it sounds like you will need a second If function, these things can go on indefinitely if you have more and more options (e.g if you want another combo to be "high risk") but I think you would need to replace the formula with this:

=IF(ISBLANK(C7),"",IF(COUNTIF(C3:C7,"No")=5,"Medium Risk","Low Risk"))

so this ^^ would be the formula in D7.

Also I assumed that you realised you need to copy the formula down the whole column as far as needed.

So now this has an added new if function which says:

If (C7), i.e. the cell to the left, isblank (i.e. is blank!) then put nothing, (i.e. ""), otherwise, if ... [the same as before (the first formula)].

Then this should just give you cells that appear blank whenever the adjacent cell is empty.

Maybe this helps you.

This topic has been archived, and won't accept reply postings.