Need Help in Countif and Average in VBA Coding.

Yes, the formula can be made easier, but it will make them harder to work with.

The simplest formula for cell B4 in Sheet 2 is:

=Sheet1!D1

And for C4:

=COUNTIF(Sheet1!D2:D50000,">3")/$C$2

The issue is that when we copy these formula down to cells B5 and C5 Excel changes them to:

=Sheet1!D2
=COUNTIF(Sheet1!D3:D50001,">3")/$C$2

Respectively, whereas we actually want them to be:

=Sheet1!E1
=COUNTIF(Sheet1!E2:E50000,">3")/$C$2

So, to get this we have to either:

a. Manually edit each formula after we've copied it to correct it, or
b. Use another formula to explain to Excel that we want a change of row in sheet 2 to represent a change of column in Sheet 1. I've used the OFFSET function to do this, but INDIRECT and TRANSPOSE are also candidates. Although, for my money, OFFSET is the most straightforward for this application.

Similiarly with the group averages, the simplest formula for C3 is;

=AVERAGE(C4:C9)

But if we copy this to C10 it gives the wrong result, because "Total rewards" has only 5 questions, compared to "Communications" 6, so we'd need to change the formula from:

=AVERAGE(C11:C16)

To:

=AVERAGE(C11:C15)

So, basically, if you make the formula simpler you're having to manually edit just about every single one to get the right results.

If you want to do that then that's up to you, but I'd suggest taking an hour or so to work out how my formula work, because they make life much easier.

In cell C3:

PHP Code: 

=AVERAGE(OFFSET(C4,,,MATCH(TRUE,INDEX(B4:B5000="",0),0)-1))