2

So here is my situation. I'm a principal creating a tracker for my teachers to use to track student growth on assessments. In the current tracker it is set up like this:

            % of            67%
            Students Met

**Student Name    Week 1 Goal:         Week 1 Actual:        Goal Met?:**
  Example A       23                    32                    Y
  Example B       45                    44                    N
  Example C       53                    55                    Y

I use a formula for the "Goal Met?" column to put a Y or N depending on if the Actual meets or exceeds the Goal column. Then, I use another formula to determine the % of students Met by doing a COUNTIF of "Y"s in the Goal Met column, dividing it by a COUNTA of that column, and multiplying by 100.

What I'm wondering is... this is very cumbersome to use for teachers, and the more columns and formulas I put, the greater the risk of teachers typing in the wrong place and messing up the formulas... is there any way I can just do a COUNTIF of the Array of the Weekly Goal and Weekly Actual columns, and count only the rows where the Actual is >= the Goal? Like is there a way to do COUNTIF(E5:F17,F>=E) and put some sort of symbol as a variable by the E and F in the criteria so that it goes row by row and compares the values, then counts the ones where the F value is greater than the E value?

Any suggestions would be great because then I can eliminate the "Goal Met" column (which they invariably type in and I have to go back and fix weekly). It gets annoying have to protect every 3rd column on a massive tracker!

Thanks so much for any advice!

Brendan

  • What about just hiding the column with the "Goal Met?" formula? – CharlieRB Dec 31 '14 at 18:25
  • Why not write-protect the cells you don't want others editing? You could even use data validation to raise a pop-up message saying "Don't edit this cell" when someone clicks on the cell. – user388043 Dec 31 '14 at 19:13
2

You should always password protect your cells that you do not want changed.

To answer you question you will need to enter an array formula. Type the following in assuming your column names starts on A3 and then hit Ctrl+Shift+Enter.

=SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)

If you did it correctly curly brackets will appear around the whole formula.

={SUM(IF(C4:C6>=B4:B6,1,0))/COUNT(C4:C6)}
  • Interesting, Microsoft must have improved the behavior of IF. When I first was trying to do this, probably with Excel 2003 or so, IF didn't handle array inputs properly. Excellent! – hBy2Py Dec 31 '14 at 19:06
  • 2
    @Brian - Excel 2003 could certainly do this (still can) - it needs "array entry", though, are you sure you were doing that? BTW you can use SUMPRODUCT and avoid the need for array entry e.g. just this "regular" formula =SUMPRODUCT((C4:C6>B4:B6)+0)/COUNT(C4:C6) – barry houdini Jan 1 '15 at 12:10
  • Wow. Seems my Excel-fu is not nearly to the level I thought. :-) I'm going to have to re-evaluate my bag of tricks, since many of them may not be relevant any more. As for IF, maybe I was using an even earlier version, Excel 2000 or 97, -- I clearly remember having a lot of trouble getting IF to work properly with array inputs. – hBy2Py Jan 1 '15 at 12:20
  • 1
    There is still a fair amount of work if the list continues to grow updating the ranges to include the new data. To automatically increase the range take a look at dynamic named ranges using the OFFSET function. Enjoy. – wbeard52 Jan 1 '15 at 17:07
  • @wbeard52 - dynamic named ranges is a good option +1, but there needn't be any work required if you don't use them - the above formulas can accommodate blank rows so there's no reason you can't use ranges that will be large enough to incorporate any expansion – barry houdini Jan 1 '15 at 20:26
0

My Solution

The following array formula, with cell references adjusted appropriately, should do what you're looking for:

=SUM(1*((OFFSET(C2,0,0,COUNT(C:C),1)-OFFSET(B2,0,0,COUNT(C:C),1))>=0))/COUNT(C:C)

Example:

Excel snip

Set the C2 reference in the first OFFSET to the top cell of the 'Actual' data (F5 in your example), the B2 reference in the second OFFSET to the top cell of the 'Goal' data (E5 in yours), and set all of the C:C references to the column containing one or the other of the 'Actual' or 'Goal' data (E:E or F:F in yours). Be sure that the only cells with numerical data in the column you choose to replace the C:C references are those cells with 'Actual' or 'Goal' values in them, otherwise the formula will either return an inaccurate result, or will fail entirely.

To enter the formula as an array formula, type it in appropriately and then press Ctrl-Shift-Enter, instead of just Enter. (See here for more information on array formulas, or just search online for "excel array formula".) Any time you make a change to the formula, you will always have to enter it with Ctrl-Shift-Enter in order for it to behave properly.

How It Works

  • Each COUNT retrieves the number of rows of data present.
  • The two OFFSET calls retrieve the two arrays of interest, the 'Actual' and 'Goal' scores. In each call:
    • The first argument marks the top-left cell of the array;
    • The second and third arguments are the row and column offsets, respectively (both zero here);
    • The fourth argument indicates the number of rows to include in the array; and
    • The fifth argument indicates the number of columns to include.
  • Since this is an array formula, subtracting the 'Actual' and 'Goal' arrays returns an array of the same size, but containing the element-wise difference between them.
  • The >=0 comparison converts the array of differences into an array of TRUE/FALSE values indicating whether or not the goal was met.
  • The 1* is there to convert the Boolean TRUE and FALSE values to numerical 1 and 0, to then be counted up by the SUM function.
    • In Excel, TRUE and FALSE implicitly carry the numerical values of one and zero.
    • BUT, if you try to pass a range or array containing TRUE/FALSE values to a function like SUM, it will ignore TRUE values and return zero.
    • SO, multiplying the whole TRUE/FALSE array by 1 is a way to trick Excel into interpreting the array in a useful way.
  • The SUM divided by the COUNT is then the desired fraction of students meeting the goals set.

EDIT: Per wbeard52's answer, it should be possible in Excel 2010 or later to replace the 1*... hack with an IF structure. In older versions of Excel, IF mishandled array inputs.

0

Another couple of array formula options, so you need to press Ctrl+Shift+Enter to commit them.

To find the number of goals met:

=SUM(--(C4:C6>B4:B6))

To find the percentage of goals met:

=SUM(--(C4:C6>B4:B6))/count(C4:C6)

You can use them independently. Remember to enter them as array formulas by pressing Ctrl+Shift+Enter.

0

Another "array formula" approach is like this:

=AVERAGE(IF(E5:E17<>"",IF(F5:F17>=E5:E17,1,0)))

confirmed with CTRL+SHIFT+ENTER

That gives you an actual % like 0.5 - if you want 50 multiply by 100

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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