Excel help please

[Eliza9919]

I've got a questionnaire, created in excel. I didn't create it, I've just been asked to help fix it. Its like a computer station risk assessment. Its got tick boxes next to each question - N/A, Yes & No. Its got a results box at the bottom that is supposed to give a risk rating (low/medium/high) but it isn't.

Does anyone know how to fix it or know what this formula or macro is called so I can google how to fix it please?

Not enough information - what exactly is in the bottom box? What’s the formula?

The bottom box has 'low' in it at the moment but when I click on it, there is no formula in there.

I can't add screenshots as I can't upload pictures at the moment.

Also what is wrong with it? The tick boxes, the results box, etc?

Any macros running in the worksheet will be attached to the worksheet. You can pull up a list of the macros and request a view of the actual code.

Are there any hidden rows or columns that the formula could be hiding in?

When you look at the cell where the results are shown, does it show a formula there or in the fx formula line about the sheet? If you share what it says (and what formulae are shown in any cell references that it refers) then it might help work out what's going on?

It may be that the writer just has a total for the Nos or Yes though that would seem to be a clumsy way of signifying a risk assessment unless the questions were created intelligently (I'm speculating but would have thought a single No/Yes could cause a problem, in some circumstances).

Looks like someone may have overwritten the formula, given your update. Is there an old saved version you could access? If not you’ll need to rewrite the formula

No hidden columns or rows.

What is wrong with it is that the box at the bottom is supposed to give a rating based on the tick box answers but it isn't.

Sorry, I didn't type fast enough. If the spreadsheet wasn't locked then it could be that the cell with the formula in has been overwritten. As there is some legal weight to the assessments, I'd be wary of trying to re-create it without reference to an older version or the author.

I didn't type faster than Iltavilli.

When you look at the cell where the results are shown, does it show a formula there or in the fx formula line about the sheet?

No, just the word low.

Is there an old saved version you could access? If not you’ll need to rewrite the formula

No, i've just been sent this one and been asked if I can fix it. I don't know how to make various answers give low/med/high. Actually, I can't do this, I don't know what the percentages are or criteria is.

Thank you all, I'll send it back and explain that I can't help.

If there is no formula the only thing you can do is rewrite the formula.

You could look for a hidden tab or white text that holds the numerics for the answers? But without knowing the parameters and the scores assigned to each answer you couldn't rewrite the formula.

It might be down to conditional formatting applied to the risk box?

I think you need to do a count function to calculate the number of ticks, then put an if/then/else function in the result box to give low / medium / high based on the result of the count function.

Look at Data - Validation. See if anything in there like an If function.

I sent it back and told him to look for a different version.

I think that sounds sensible. The difficult thing wouldn't be the formula, rather knowing what answers should give a high or low rating. As there could be legal consequences from incorrect assessments and personal injury, I wouldn't try and recreate any functionality without clear, audible instructions (I sound like a jobsworth, I know).

The formula will likely be a simple risk equation - likelihood of something happening x impact of it happening.

However, it can be as simple as a 3x3 matrix (so scoring 1 to 9) or as complex as you like it, I've seen a 10x10 used before (so scoring 1 to 100); the only person who can tell you what the values and weighting are will be the person who has done the initial risk assessments on your computer workstations. All they have done is made a spreadsheet to simplify their work for either display or the sheer volume of times they need to do it.

You can't "fix" it without knowing what weighting they applied. Send it back to your H&S rep and tell them to do their job, before you get shafted when you've given everyone bad backs, eye strain and carpal tunnel syndrome because you were trying to help out.