Data Analysis using Excel
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
Last edited by Cmhumphrey; 11-25-2010 at 04:49 AM.
Is this what you mean
=COUNTIF(A2:C2,"<>0")*3/E2
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks