/ Excel Help for a dummy
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.
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.
=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
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
Cheers Snyggapa, much appreciated
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.
A fire on Marsden Moor in West Yorkshire, which has now affected around 15km2 of moorland, is likely to have been started by a barbecue