Help with a function in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1540722%22%20slang%3D%22en-US%22%3EHelp%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540722%22%20slang%3D%22en-US%22%3EI'm%20trying%20to%20make%20a%20formula%20that%20will%20average%20a%20range%20of%20cells%2C%20but%20only%20if%20those%20cells%20are%20labeled%20by%20a%20specific%20month%20(%22Jan%22).%20I've%20tried%20the%20if%20function%20%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(B8%3ABB8%3D%22Jan%22)%2CAVERAGE(B9%3ABB9)%2C%22-%22%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20I%20keep%20getting%20the%20%22%23VALUE!%E2%80%9D%20error%20in%20the%20cell%20where%20the%20answer%20would%20be.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1540722%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540831%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540831%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737550%22%20target%3D%22_blank%22%3E%40Gossemer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20the%20newest%20version%20of%20Excel%2C%20you%20can%20do%20this%20easily%20with%20the%20new%20Dynamic%20Array%20function%20called%20FILTER.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formula%3A%20%3CSTRONG%3E%3DAVERAGE(FILTER(B9%3ABB9%2CB8%3ABB8%3D%22Jan%22))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540842%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540842%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737550%22%20target%3D%22_blank%22%3E%40Gossemer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20the%20FILTER%20function%20doesn't%20work%20with%20your%20version%20of%20Excel%2C%20try%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DSUMIF(B8%3ABB8%2C%22Jan%22%2CB9%3ABB9)%2FCOUNTIF(B8%3ABB8%2C%22Jan%22)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1540965%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1540965%22%20slang%3D%22en-US%22%3EThis%20seemed%20to%20actually%20produce%20something!%20Which%20is%20a%20start.%20However%20if%20there%20are%205%20days%20worth%20of%20scores%20in%20January%20(%22Jan%22)%20and%20say%20you%20got%20a%2060%20I'd%20want%20the%20average%20to%20come%20to%2060.%20Is%20there%20a%20way%20to%20make%20it%20average%20only%20filled%20in%20cells%20for%20cells%20containing%20%22Jan%22%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541098%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541098%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F737550%22%20target%3D%22_blank%22%3E%40Gossemer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20wrote%3A%20%3CEM%3EThis%20seemed%20to%20actually%20produce%20something!%20Which%20is%20a%20start.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20which%20I%20need%20to%20respond%3A%20%22Which%20'%3CFONT%20color%3D%22%23FF0000%22%3Ethis%3C%2FFONT%3E'%20are%20you%20referring%20to%3F%22%20I%20gave%20you%20two%20separate%20ways%20of%20doing%20what%20you'd%20asked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20then%20wrote%3A%20%3CEM%3EHowever%20if%20there%20are%205%20days%20worth%20of%20scores%20in%20January%20(%22Jan%22)%20and%20say%20you%20got%20a%2060%20I'd%20want%20the%20average%20to%20come%20to%2060.%26nbsp%3B%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20which%20my%20first%20response%20is%2C%20at%20least%20in%20my%20own%20example%20on%20my%20Mac%2C%20the%20first%20solution%20I%20gave%20you%20(using%20FILTER)%20%3CU%3E%3CSTRONG%3Edoes%20produce%20that%2060%3C%2FSTRONG%3E%3C%2FU%3E%20if%20there's%20only%20one%20entry.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAfter%20which%20I'd%20continue%20with%20wondering%2C%20%22if%20there%20are%20five%20days%20worth%20of%20scores%20in%20January%20and%20you%20got%20a%2060%22%20isn't%20the%20average%20actually%2012%20(else%20why%20are%20we%20saying%20there%20are%20%22five%20days%20worth%20of%20scores%22).%26nbsp%3B%26nbsp%3B%20If%20what%20you're%20saying%20instead%20is%20that%20%22Jan%22%20gets%20mentioned%20five%20times%2C%20but%20there's%20in%20fact%20only%20one%20day%20in%20Jan%20with%20a%20score%2C%20that's%20something%20different.%3C%2FP%3E%3CP%3ESee%2C%20the%20problem%20here%20is%20that%20the%20English%20language%20is%20delightfully%20ambiguous--and%20we%20may%20or%20may%20not%20understand%20what%20one%20another%20are%20saying%20even%20though%20each%20of%20us%20thinks%20of%20himself%20as%20crystal%20clear.%20Writers%20love%20to%20play%20with%20words%20precisely%20because%20of%20the%20possibility%20of%20multiple%20meanings%2C%20and%20usually%2C%20in%20person%2C%20we're%20able%20to%20understand%20one%20another%20from%20the%20bigger%20context%2C%20from%20nuances%20in%20body%20language%2C%20etc.%3C%2FP%3E%3CP%3EComputers%2C%20however%2C%20are%20pretty%20literal.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyway%2C%20if%20that%20first%20response%20doesn't%20work%20on%20your%20computer%20(FILTER%20is%20only%20available%20in%20the%20newest%20Excels)%2C%20I'd%20suggest%20playing%20around%20more%20with%20the%20COUNT%20functions%20for%20the%20divisor%20to%20calculate%20your%20average%20only%20based%20on%20numbers%20of%20numbers%20under%20%22Jan%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541139%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541139%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAVERAGEIFS()%20is%20another%20option%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1541350%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20a%20function%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1541350%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIf%20what%20you're%20saying%20instead%20is%20that%20%22Jan%22%20gets%20mentioned%20five%20times%2C%20but%20there's%20in%20fact%20only%20one%20day%20in%20Jan%20with%20a%20score%2C%20that's%20something%20different.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20exactly%20what%20I'm%20looking%20for.%20at%20the%20moment.%20January%20has%205%20weeks%2C%20but%20only%202%20are%20filled.%20so%20essentially%20although%20there%20are%20five%20weeks%20in%20the%20month%20it%20should%20add%20the%20two%20scores%20and%20divide%20by%20two.%20As%20I%20add%20scores%2C%20it%20should%20divide%203%20scores%20by%203%20and%20so%20on.%20I%20don't%20have%20the%20FILTER%20function.%20so%20I%20am%20still%20having%20some%20issues.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor
I'm trying to make a formula that will average a range of cells, but only if those cells are labeled by a specific month ("Jan"). I've tried the if function :

=IF(B8:BB8="Jan"),AVERAGE(B9:BB9),"-"

But I keep getting the "#VALUE!” error in the cell where the answer would be.
6 Replies

@Gossemer 

 

If you have the newest version of Excel, you can do this easily with the new Dynamic Array function called FILTER.

 

Here's the formula: =AVERAGE(FILTER(B9:BB9,B8:BB8="Jan"))

 

 

 

 

@Gossemer 

 

If the FILTER function doesn't work with your version of Excel, try this:

 

=SUMIF(B8:BB8,"Jan",B9:BB9)/COUNTIF(B8:BB8,"Jan")

 

 

This seemed to actually produce something! Which is a start. However if there are 5 days worth of scores in January ("Jan") and say you got a 60 I'd want the average to come to 60. Is there a way to make it average only filled in cells for cells containing "Jan"?

@Gossemer 

 

You wrote: This seemed to actually produce something! Which is a start. 

 

To which I need to respond: "Which 'this' are you referring to?" I gave you two separate ways of doing what you'd asked.

 

You then wrote: However if there are 5 days worth of scores in January ("Jan") and say you got a 60 I'd want the average to come to 60. 

 

To which my first response is, at least in my own example on my Mac, the first solution I gave you (using FILTER) does produce that 60 if there's only one entry.

 

After which I'd continue with wondering, "if there are five days worth of scores in January and you got a 60" isn't the average actually 12 (else why are we saying there are "five days worth of scores").   If what you're saying instead is that "Jan" gets mentioned five times, but there's in fact only one day in Jan with a score, that's something different.

See, the problem here is that the English language is delightfully ambiguous--and we may or may not understand what one another are saying even though each of us thinks of himself as crystal clear. Writers love to play with words precisely because of the possibility of multiple meanings, and usually, in person, we're able to understand one another from the bigger context, from nuances in body language, etc.

Computers, however, are pretty literal.

 

Anyway, if that first response doesn't work on your computer (FILTER is only available in the newest Excels), I'd suggest playing around more with the COUNT functions for the divisor to calculate your average only based on numbers of numbers under "Jan"

@mathetes 

AVERAGEIFS() is another option

@mathetes 

 

If what you're saying instead is that "Jan" gets mentioned five times, but there's in fact only one day in Jan with a score, that's something different.

 

This is exactly what I'm looking for. at the moment. January has 5 weeks, but only 2 are filled. so essentially although there are five weeks in the month it should add the two scores and divide by two. As I add scores, it should divide 3 scores by 3 and so on. I don't have the FILTER function. so I am still having some issues.