average ifs

[bobby769]

Hello,

I need to get the average of numbers based on 2 different criteria.
In the attached spreadsheet I already have the average based on date but now I need to get the average based on date but the average of values that are below a certain number.

Columns A, B, and C have the data.
The light blue section is what I have and the dark blue cell shows the formula I'm using to get it.

Now I need to get the average of the values in Column C but the average of only some of those values.

I hope I'm explaining myself clearly.

[:) Sixthsense :)]

Try this Non Array Formula

=AVERAGEIFS(DAYS_TO_CLOSE,DAYS_TO_CLOSE,"<=35",DATE_CLOSED,">="&VALUE("01-"&E2&"-"&F2),DATE_CLOSED,"<="&EOMONTH(VALUE("01-"&E2&"-"&F2),0))

[FDibbins]

What is the highlighting based on, because regular formulas cannot "see" color.

Also it would probably make things simpler if you used a month number, instead of text?

edit: OK, so maybe I should read the info properly lol, sorry

[bobby769]

Try this Non Array Formula

=AVERAGEIFS(DAYS_TO_CLOSE,DAYS_TO_CLOSE,"<=35",DATE_CLOSED,">="&VALUE("01-"&E2&"-"&F2),DATE_CLOSED,"<="&EOMONTH(VALUE("01-"&E2&"-"&F2),0))

Thank you so much!!
the only thing i had to change was the <=35 to just <35.

[bobby769]

Also it would probably make things simpler if you used a month number, instead of text?

The above answer is working for me but out of curiosity,
what would the formula be if I changed the date format to text?

[FDibbins]

Instead of using something like this...
VALUE("01-"&E2&"-"&F2) or TEXRT() to convert part iof teh date to text, you would just use real dates inthe AVERAGEISs() to pull in the start and end dates

Its normally a good idea to keep your data types the same, it simplifies the formulas