Forum Discussion
Help with a function in Excel
=IF(B8:BB8="Jan"),AVERAGE(B9:BB9),"-"
But I keep getting the "#VALUE!” error in the cell where the answer would be.
6 Replies
- 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"?
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"
