3

I have 6 columns.

Each column has a NA, 1 or 0 entered.

I need column7 to calculate a score (preferably as a percentage), however NA should not be included in the count.

EG

1,  1,  1,  0,  NA, 1  =  4/5

0,  0,  0,  1,  1,  1 = 3/6

NA,  NA,  1, 1, 1, 1 = 4/4
  • If Atish answered your question, then please mark it as answered. – Danny '365CSI' Engelman Feb 6 '16 at 21:37
2

Let's say, six columns are C1, C2, C3, C4, C5, C6. So now you can just check the value before adding and dividing. I mean following

=(
   IF(C1="NA",0,C1) +
   IF(C2="NA",0,C2) +
   IF(C3="NA",0,C3) +
   IF(C4="NA",0,C4) +
   IF(C5="NA",0,C5) +
   IF(C6="NA",0,C6) 
 )/
 ( 
  IF(C1="NA",0,1) + 
  IF(C2="NA",0,1) +
  IF(C3="NA",0,1) +
  IF(C4="NA",0,1) +
  IF(C5="NA",0,1) +
  IF(C6="NA",0,1) 
 )

Explanation

  1. IF(C1="NA",0,C1) means It will add 0 if C1 is NA. Otherwise it will add C1 itself. So NA is no more in the sum.

  2. IF(C6="NA",0,1) mean the count will not increase if the value is NA

Output using question's Input

enter image description here

If you need (A/B) type output, then you can use following formula:

=CONCATENATE(
 (
   IF(C1="NA",0,C1) +
   IF(C2="NA",0,C2) +
   IF(C3="NA",0,C3) +
   IF(C4="NA",0,C4) +
   IF(C5="NA",0,C5) +
   IF(C6="NA",0,C6) 
 ),
 "/",
 (
  IF(C1="NA",0,1) + 
  IF(C2="NA",0,1) +
  IF(C3="NA",0,1) +
  IF(C4="NA",0,1) +
  IF(C5="NA",0,1) +
  IF(C6="NA",0,1) 
 )
)

In that case output will be like following

enter image description here

| improve this answer | |
  • Thanks for your reply. however I don't believe this worked in a calculated column in SP. – Blake Feb 4 '16 at 4:00
  • Did you try?.... – Atish Dipongkor - MVP Feb 4 '16 at 4:09
  • There was a extra plus sign (+). I have updated my answer – Atish Dipongkor - MVP Feb 4 '16 at 5:58
  • Hello, This formula did not work in my calculated column. I have received this error:The formula contains a syntax error or is not supported. Maybe I need to create a Nintex workflow instead of a calculated column.. – Blake Feb 7 '16 at 22:17
  • Did you change the column names properly? – Atish Dipongkor - MVP Feb 8 '16 at 3:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.