Formula for average using a control source

%3CLINGO-SUB%20id%3D%22lingo-sub-1853362%22%20slang%3D%22en-US%22%3EFormula%20for%20average%20using%20a%20control%20source%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1853362%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20get%20an%20average%20using%20data%20stored%20in%20cells%20running%20across%20the%20page.%20There%20will%20be%20one%20row%20of%20information.%26nbsp%3B%20The%20column%20headings%20are%20the%20three%20letter%20month%20abbreviation%20starting%20with%20OCT%20and%20ending%20with%20SEP.%26nbsp%3B%20I%20would%20like%20to%20create%20a%20formula%20that%20looks%20at%20the%20entire%20range%20(columns%20from%20OCT%20-%20SEP)%20while%20disregarding%20the%20current%20month%20and%20discounting%20any%20'0's.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20for%20the%20month%20of%20NOV%20the%20AVG%20would%20be%20what%20ever%20is%20in%20the%20column%20for%20the%20corresponding%20row%20in%20OCT.%26nbsp%3B%20Similarly%2C%20if%20we%20were%20in%20JUN%20the%20formula%20would%20average%20the%20data%20in%20the%20columns%20OCT%20-%20MAY.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20this%20be%20an%20array%20formula%3F%26nbsp%3B%20Or%20am%20I%20overthinking%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-11-04_08h37_15.png%22%20style%3D%22width%3A%20665px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F231454iB20853D9B267E4B0%2Fimage-dimensions%2F665x40%3Fv%3D1.0%22%20width%3D%22665%22%20height%3D%2240%22%20role%3D%22button%22%20title%3D%222020-11-04_08h37_15.png%22%20alt%3D%222020-11-04_08h37_15.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1853362%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1853696%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20average%20using%20a%20control%20source%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1853696%22%20slang%3D%22en-US%22%3E%3CP%3E%3DAVERAGE(OFFSET(B1%2C0%2C0%2C100%2CMATCH(TEXT(TODAY()%2C%22MMM%22)%2CB1%3AM1%2C0)-1))%3CBR%20%2F%3Eassuming%3A%3CBR%20%2F%3E1.%20your%20table%20starts%20in%20B1%20and%20it's%20100%20rows%20high%3CBR%20%2F%3E2.%20%22MMM%22%20is%20the%20proper%20short%20Month%20format%20code%20for%20your%20language%20settings%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E**ED**%3C%2FP%3E%3CP%3EForgot%20-1%3C%2FP%3E%3CP%3E%2F**ED**%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I am trying to get an average using data stored in cells running across the page. There will be one row of information.  The column headings are the three letter month abbreviation starting with OCT and ending with SEP.  I would like to create a formula that looks at the entire range (columns from OCT - SEP) while disregarding the current month and discounting any '0's.

 

So for the month of NOV the AVG would be what ever is in the column for the corresponding row in OCT.  Similarly, if we were in JUN the formula would average the data in the columns OCT - MAY.

 

Would this be an array formula?  Or am I overthinking it?

 

2020-11-04_08h37_15.png

2 Replies
Highlighted

=AVERAGE(OFFSET(B1,0,0,100,MATCH(TEXT(TODAY(),"MMM"),B1:M1,0)-1))
assuming:
1. your table starts in B1 and it's 100 rows high
2. "MMM" is the proper short Month format code for your language settings

 

**ED**

Forgot -1

/**ED**

Highlighted

@MindreVetandeThank you, and for the ED -- was scratching head as I was getting #N/A error.  It works just right!