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:
n
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
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)?
Bookmarks