AVERAGEIFS formula when evaluating not being equal to a number with leading zeros

[Shellecj]

Can someone please clarify why I'm having trouble with getting accurate results with the following formula? The formula will average the amount of ALL sales that took place within the specified date range in the formula (named cell ranges "begin_date" & "ending_date") and not eliminate the records with the ID of 001.

=AVERAGEFS(A1:A100,B1:B100,"<>001",C1:C100,">="&begin_date,C1:C100,"<="&ending_date) Note: I did also try "<>"&"001" to no avail.


On the contrary the formula seems to work fine when averaging the range for all that ARE equal to 001 (i.e. entering the formula in this form: =AVERAGEFS(A1:A100,B1:B100,"001",C1:C100,">="&begin_date,C1:C100,"<="&ending_date) ) but for some reason it will not work when using the operator <>.

I played around with the formula and tried substituting all ID's that were 001 with a text string of abc and then replaced the 001 in the formula with abc and then finally it worked.

Another note to point out is the "001" is the result of a formula that is extracting a 3 digit code from a string in another column elsewhere in the sheet using the LEFT function.

 

[Marcelo Branco]

is this a typo?
=AVERAGEFS(A1:A100,

worked perfectly for me
=AVERAGEIFS(A1:A100,B1:B100,"<>001",C1:C100,">="&begin_date,C1:C100,"<="&ending_date)

M.

 

[Shellecj]

Sorry that was a typo. For some reason Copy and Paste wasn't working so I manually typed that in. I do have it typed correctly in my worksheet.

I don't know why it's not working for me though? But again, where that ID code is being referenced from is a column on another worksheet that is also populated with a formula ( =LEFT(A2,3) )

 

[Marcelo Branco]

What you mean by is not working? An error or wrong result?

Could you provide a data sample, say, 10 rows and expected result?

M.

 

[Shellecj]

No, no error..it will give me the average of ALL records within those dates, including records with the ID's of 001

I can try to extract a sample of my data to a test sheet later and upload. Don't have something like that readily available at the moment other than my actual data which is confidential material. I'll circle back to this thread when I can to upload some kind of sample to evaluate.

Thanks Marcelo!

 

[Aladin Akyurek]

Try rather:

=AVERAGEIFFS(A1:A100,B1:B100,"<>??1",C1:C100,">="&begin_date,C1:C100,"<="&ending_date)