Increment Cell Address every other Column (Currently skips)

[jtnb]

I want to have columns that give the average and standard deviation for cell parameters, using input from another sheet.

The input sheet (Sheet1) looks like:

Item #	Location 1	Location 2	Location 3
1	#	#	#
1	#	#	#
1	#	#	#
2	#	#	#
2	#	#	#
2	#	#	#
2	#	#	#
2	#	#	#
2	#	#	#

And I want the output (Sheet2) to be

Item #	Loc1 Avg	Loc1 StD	Loc2 Avg	Loc2 StD	Loc3 Avg	Loc3 StD
1	#	#	#	#	#	#
2	#	#	#	#	#	#

I have a problem when I try to autofill. For average and StDev, I'm using the following formulas:

=AVERAGE(IF(Sheet1!$A$2:A$1000$=$A2, Sheet1!B$2:B$1000))
=STDDEV.S(IF(Sheet1!$A$2:A$1000$=$A2, Sheet1!B$2:B$1000))

(This next part is abbreviated for readability - D2:D1000 means the formula in that cell reads =AVERAGE(IF(Sheet1!$A$2:A$1000$=$A2, Sheet1!D$2:D$1000)) )

The autofill works vertically, but when I try to autofill horizontally, it increments the input address by 2 instead of by 1, so instead of getting a B B C C D D... pattern:

Item #	Loc1 Avg	Loc1 StD	Loc2 Avg	Loc2 StD	Loc3 Avg	Loc3 StD
1	B2:B1000	B2:B1000	C2:C1000	C2:C1000	D2:D1000	D2:D1000
2	B2:B1000	B2:B1000	C2:C1000	C2:C1000	D2:D1000	D2:D1000

n


I get a B B D D F F ... pattern:

Item #	Loc1 Avg	Loc1 StD	Loc2 Avg	Loc2 StD	Loc3 Avg	Loc3 StD
1	B2:B1000	B2:B1000	D2:D1000	D2:D1000	F2:F1000	F2:F1000
2	B2:B1000	B2:B1000	D2:D1000	D2:D1000	F2:F1000	F2:F1000

It always skips a column when I autofill. I can't get it to just increment 1 input column every 2 output columns, no matter how many I input manually to establish the pattern. Is there a way I can get excel to stop skipping input columns (Just do A A B B C C etc)?

[JohnTopley]

Try

in B2

=AVERAGE(IF(Sheet1!$A$2:$A$1000=$A2,INDEX(Sheet1!$B$2:$Z$1000,,INT((COLUMNS($A:A)-1)/2+1))))

in c2

=STDEV.S(IF(Sheet1!$A$2:$A$1000=$A2,INDEX(Sheet1!$B$2:$Z$1000,,INT((COLUMNS($A:A)-1)/2+1))))



...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Select B2 and C2 and drag across and down