Welcome Guest ( Log In | Register )

Outline · [ Standard ] · Linear+

 Excel Calculation

views
     
TSsunakujiro^^
post Dec 17 2018, 01:48 PM, updated 6y ago

schutzstaffel
****
Senior Member
652 posts

Joined: Jan 2003
From: マレーシアと日本とドイツです


Hi, I have a form that calculates each item as YES, NO, NA.

From the attached excel, I have 18 items under parameter BS.

How the calculation should works:

If all 18 Items are either YES or NO, it should sum and divide by 18 items.

But Im trying to figure out, if out of 18 items, 8 items are categorized as N/A, then the calculation should be Sum and Divide 10 items. (18 items: 10 items = Yes, 8 = N/A , total = 100%)


Any item categorized as N/A should not be counted.

Could you kindly guide me how I can do that?



Attached File(s)
Attached File  Audit_calc.zip ( 27.24k ) Number of downloads: 7
onetwoswitch
post Dec 17 2018, 02:04 PM

Getting Started
**
Junior Member
209 posts

Joined: Feb 2018
Hi there, before working on the file, would like to make sense of the situation first. Based on the explanation given, as long as the field is "YES" or "NO", you want it to be counted, and if the field is "N/A", you want it to be ignored. That is understood.

But for the denominator, if the number taken is also ignoring "N/A", you would be always getting 100% right? For easier explanation:

A) ("YES" + "NO") / ("YES" + "NO")

Do you actually mean?

B) ( "YES" ) / ("YES" + "NO")

I think example B sounds more relevant.
TSsunakujiro^^
post Dec 17 2018, 02:26 PM

schutzstaffel
****
Senior Member
652 posts

Joined: Jan 2003
From: マレーシアと日本とドイツです


Hi, thanks for the reply.

Indeed example B is more relevant.

Scenario 1

Item 1 = 5 = Yes
Item 2 = 5 = No
Item 3 = 5 = Yes

Calculation: (5 + 5) / 15

Scenario 2

Item 1 = 5 = Yes
Item 2 = 5 = N/A
Item 3 = 5 = Yes

Calculation: (5 + 5) / 10
TSsunakujiro^^
post Dec 17 2018, 03:57 PM

schutzstaffel
****
Senior Member
652 posts

Joined: Jan 2003
From: マレーシアと日本とドイツです


QUOTE(onetwoswitch @ Dec 17 2018, 02:04 PM)
Hi there, before working on the file, would like to make sense of the situation first. Based on the explanation given, as long as the field is "YES" or "NO", you want it to be counted, and if the field is "N/A", you want it to be ignored. That is understood.

But for the denominator, if the number taken is also ignoring "N/A", you would be always getting 100% right? For easier explanation:

A) ("YES" + "NO") / ("YES" + "NO")

Do you actually mean?

B) ( "YES" ) / ("YES" + "NO")

I think example B sounds more relevant.
*
I think i managed to ignore any items "N/A" out of calculation for each parameter, however it raised another problem when comes to FINAL SCORE.

There are 5 Parameters namely (BS, MC, SD,CC,GLI)

FINAL Score calculation is : Sum of(BS, MC, SD,CC,GLI) div 5

Scenario 1.

If all item for any parameter are N/A .. that parameter should not be counted.

e.g All item for BS is N/A.

Final score should be Sum of(MC, SD, CC, GLI) div 4.

However I got the error #DIV0!

Could you please kindly help

onetwoswitch
post Dec 17 2018, 04:32 PM

Getting Started
**
Junior Member
209 posts

Joined: Feb 2018
Hope this is what you are looking for.


Attached File(s)
Attached File  Audit_calc2.zip ( 26.53k ) Number of downloads: 6
TSsunakujiro^^
post Dec 17 2018, 04:32 PM

schutzstaffel
****
Senior Member
652 posts

Joined: Jan 2003
From: マレーシアと日本とドイツです


Duplicate post

This post has been edited by sunakujiro^^: Dec 17 2018, 04:42 PM

 

Change to:
| Lo-Fi Version
0.0137sec    0.15    6 queries    GZIP Disabled
Time is now: 29th March 2024 - 04:08 AM