Calculating a number from answers to an audit

**BigFish** · 12-18-2008, 05:13 PM

I have created a workbook that I store data from my audits, this data is in the form of Y if compliant, N if noncompliant and N/A if not applicable. Where the fun part begins is that each question has a different risk involved. I have used a simple 1 to 5 risk scores and given scores for compliance and non compliance to each score, for example a risk 1 if compliant is 100 points, if non compliant is -100 points, all the N/As are worth 0.

I currently calculate the totals in a different sheet in the work book, but I do this kind of manually, I have calculations to work out the totals and percentages and all that, but I cannot figure out how to get the Ys and Ns to appear in this sheet as 100 or -100. All I do at the moment is bring the Y, N or N/A over with a simple =corresponding cell in sheet 1 then manually change this to the number I require.

Would anyone be able to help me to automate this so that when I put the Y, N or N/A in sheet 1 it automatically puts 100, -100 or 0 into the correct cell in sheet 2.

If you need extra information to help with this I can provide anything.

**Richard Buttrey** · 12-18-2008, 05:15 PM

Hi and welcome to the forum.

Upload a workbook with a sample of your data. It always helps if we can see the requirement in context.

Rgds

**BigFish** · 12-28-2008, 09:59 PM

I have been making attempts to post this but the company's IT policy keeps stopping me, I will attempt to attach it tonight from home.

When I attach the spreadsheet the below will make more sense, but maybe not. I am basically trying to populate a cell from a table using criteria from 2 areas, I might be going about this the wrong way, but really want to find a way that does not mean I have to manually change the data as I do currently.

What I am trying to do is to get the numbers in the Risk Scores worksheet to automatically fill when the data is entered into the corresponding cell in the Audit Compliance Reg worksheet.

On the Risk Score worksheet there is a number that shows the risk score for that question, this relates to the table in the Table worksheet.

Using the Y, N or N/A answer on the Audit Compliance Reg work sheet and the score on the Risk Score Worksheet, the table will give the score for that question; I just can't get it to work automatically.

**Mark@Work** · 12-29-2008, 12:29 AM

Why won't a simple (nested) IF work?

=IF(A1="Y",100,IF(A1="N",-100),0)

**BigFish** · 12-29-2008, 05:02 PM

Thank You Mark, This mostly works.

Firstly the reason I did not think of it is that I have this massive problem of trying to find the hard solution when there is an easy one.

Secondly I had to take the 0 out of the function, with that it would not give the negative, so without the 0 it gives a error with no text or an N/A but this is easy to get around.

Again thank you for your help.

Calculating a number from answers to an audit

LinkBack

Thread Tools

Rate This Thread

Display

Calculating a number from answers to an audit

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions