0

I have a data for an assessment tool that has about 20 categories and about 200 items scored 0-3. The first half of the tool is mandatory, other sections are optional so there are a lot of blank areas. 4 items have the option of N/A.

I currently use arrays to gather average if data over multiple columns for each category. I got help on another forum and am including the formulas below as well. I use two different ones, as mentioned in some of the data ranges all items cells require a score, and for some sections, it is optional or not scored at all. In the sample grid below, I need to find the average for all the data columns combined (Q1-Q4) based one Assessment types B and N. Note: some scores are given option for n/a

T   Q1  Q2  Q3  Q4
N   0   0   3   2
B   1   2   3   
N   1   1   0   0
N       
D   0   n/a 2   3
O1  0   1   1   0
O1  2   0   0   0
O2  2   n/a 0   0

The grid I pasted here doesn't seem to show correctly and I can't paste an image of it. Below it doesn't resemble my grid at all. So let's say that in column A I have different codes for assessment type. B, D, N, O1, O2. In columns B-E I have scores for each question. Some cells are blank for one whole row, a couple of rows in column C are marked N/A. So row two might be: N 0 1 3 0; row two might be B 0 N/a 2 2. Row three might be B (empty cells)

Current formulas for using one criteria:

=AVERAGE(IF(ANSAt!H:H="B",ANSAt!J:AC))

and to not include blanks in sections that have lots of blanks

=AVERAGE(IF(ANSAt!$H:$H="B", IF(XMod!G:Y<>"", XMod!G:Y)))

If anyone can help me show the example I would appreciate it :) I even tried to use the Snip tool.

1 Answer 1

0

If you can sort on the "T" column then you can use this:

=AGGREGATE(1,6,INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0),1):INDEX($B$2:$E$9,MATCH(H2,$A$2:$A$9,0)+COUNTIF($A$2:$A$9,H2)-1,4))

enter image description here

EDIT:

While you are trying to understand the above I thought I would add one more for you. This formula does not care if it is sorted or not:

=SUM(IF(ISERROR(INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)),"",INDEX(($A$2:$A$9=H2)*$B$2:$E$9,)))/SUMPRODUCT(--(INDEX(($A$2:$A$9=H2)*ISNUMBER($B$2:$E$9),)))

This is an array formula. It needs to be confirmed with Ctrl-Shift-Enter when leaving edit mode. If done properly Excel will put {} around the formula.

Also it you want to hard code either of these formula with the lookup value directly replace the H2 reference with the desired String; "N".

enter image description here

4
  • That's pretty complicated! I haven't yet figured out where it says to average the data section if row A has B or N. Although you said to sort, so maybe that's where I'm missing something. I've asked a colleague to look at this too since she gets it more than me :)
    – Avanell
    Apr 12, 2016 at 22:59
  • @Avanell I put it in a small table in H and I. The formula is pasted in I and refers to H, If you want to hard code the lookup then change all the references of H2 to "N" or "B" or whatever. there are three references that need to be changed. Apr 12, 2016 at 23:49
  • We kind of get it...haven't tried out yet but will :)
    – Avanell
    Apr 15, 2016 at 1:23
  • @Avanell what are your concerns, about trying it? Also see the second formula I just put up. It works without sorting. Apr 17, 2016 at 21:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .