Excel 2007 : Data Analysis using Excel

**Cmhumphrey** · 11-24-2010, 01:24 PM

Hi Guys,

I am new to the forum and hope you can help.

I work as an auditor and have recently been set the challenge of designing a means of reporting on my findings back to my line management and believed after studying the options that excel was my best bet for data management and analysis.

Unfortunately I have run into a stumbling block in the form of formulae and wondered if any of you could help me find a solution.

The Audits I perform are scored as follows:-

3 = Pass
2 = Minor Non-conformance
1 = Major Non-conformance/Stop Work
0 = N/a

At this point if you could refer to the attached image

The columns A,B,C are the ones I input my scores in.

The column D currently has the autosum =SUM(A1:C1)/(E1) and is formatted to percentage.

What I require is a formula that will interpret a score of 1 or 2 in any of the cells in columns A,B,C as a 3 for the purposes of column E as this is a Maximum Total Audit point score and therefore should be 3 x the number of sites as long as there is not a 0 in the cell in which case that cell is not counted towards the total (please see row highlighted blue).

I have considered manual entry in column E but potentially this excel table will be used to compare performance across 100+ sites.

I believe I have tried all the standard formula in excel but don’t have a clue where to start with array formula or macro’s.

If any of you can offer any advice or have a formula that will perform this calculation for me it would be greatly appreciated

Regards

Chris

**Bob Phillips** · 11-24-2010, 02:28 PM

Is this what you mean

=COUNTIF(A2:C2,"<>0")*3/E2

**Cmhumphrey** · 11-24-2010, 03:33 PM

Bob,

Many thanks for your response unfortunatley this does not give the result I am looking for in column E an column E can only have a multiple of 3 in it so the equation for it on paper would be 3n-(3c) where c=0, (3 x n - 3 x c) where n is the number of columns that represent sites and c is a cell in the row that = 0 or to use numbers 3 x 4 - (3 x 2). i can do the basic maths but cant get this to translate ito an auto sum

Regards

Chris

**Cmhumphrey** · 11-25-2010, 05:47 AM

Thanks to all those who looked at this to try and solve it i have figured out the formula its

=COUNTIFS(F14:K14,"<>0",F14:K14,"<>0")*3

Regards

Chris