Only include numbers in a certain range for AVERAGEIFS

**news98** · 10-23-2020, 02:53 PM

I'm trying to make a formula that averages the prices of vehicles that match the year, make, model, and are within +-10,000 miles of the odometer reading. I'm having trouble with implementing the +- part of the mileage. I've tried (H:H,">"&DJ2&"-10000",H:H,"<"&DJ2&"+10000") where H is the column with the odometer values and cell DJ2 is the odometer reading being used for reference. I am very much a novice, so any help would be appreciated.

**Limor_OP** · 10-23-2020, 03:00 PM

Hi
can you please attached the file with the formula that is not working?

**news98** · 10-23-2020, 03:21 PM

Sorry, the file I'm working with is quite large so I didn't want to include it. This smaller version should have the relevant information though. Cell L2 is the full formula I'm trying, cell N2 is the formula without including anything about the odometer, to show it works otherwise. As you can see, if I had the accurate formula for what I'm attempting both rows 12 and 15 would fit the criteria.

**MrShorty** · 10-23-2020, 03:43 PM

I see confusion over math and text operators. +/- are the math operators for addition and subtraction. & is the concatenate operator for joining text strings. Using the evaluate formula tool (if you are unfamiliar: https://support.microsoft.com/en-us/...rs=en-us&ad=us ), I can see that your formula is attempting to find the average for records where the odometer reading (a number) is between the text strings "158579-10000" (which does NOT evaluate to 148579) and "158579+10000 (which also does not evaluate to 168579). A number will never be between two text strings, so the result is Div/0.

Solution is to get the correct operators in your criteria arguments. Something like averageifs(...,H:H,">"&DJ2-10000,H:H,"<"&DJ2+10000). See if that helps.

**news98** · 10-23-2020, 03:53 PM

That did the trick! Thank you. I had no idea what the & was actually doing, I only saw it was necessary to get the cell recognized in the formula by trial and error. This explanation is a great help!