Finding Average Unless Any Cell Is Blank. If Blank return "Alert"

[Mizzou.2018]

I am needing help figuring out a formula to find an average, but if one or more of the cells in the range are blank then I need the word "Alert" returned. There are no 0's in the data, but I would still like to capture that as a number in the event there is a 0 in the future. So the only time there would not be an average calculated is if one or more cells in the range is blank.

Range:
K2:O2

Thanks so much!

[Melvosh]

Try this:

Please Login or Register  to view this content.

Does that give you the results you're looking for?

[Mizzou.2018]

It did!! Thank you so much! I tried and tried but couldn't figure it out.

I want to take it a step farther and capture the average of the last 6 months. As a new month is added to the worksheet it would use it as month 1 and then only use 5 other months to total 6 months. Blanks will not be an issue as the Alert formula above took care of that, but if the cell says "Alert" then it should return "Alert" instead of an average.

[Melvosh]

That would greatly depend on how your data is organized. If you can upload a small sample workbook, showing your data layout and some example data, that would allow us to provide specific assistance. In order to upload, click Go Advanced, then Manage Attachments. Click Browse to find your file, click Open, then click Upload. Click Close this window, and your file will be uploaded when you submit your reply.

[Mizzou.2018]

Absolutely! I included the attachment. As months go by, the information is entered to the bottom of the worksheet then sorted by ID (smallest to largest) then Data Date (newest to oldest).

Thank you

[Melvosh]

I want to clarify a bit how you want to handle blanks for the 6 month rolling average. I think you want to show "Alert" if there are any blanks in the 6 months for a given ID. Is that correct?

If that's the case, it looks like there would only be one 6 month average that would not show "Alert" for this data set. I'm guessing your actual data set is much larger.

[Mizzou.2018]

Correct, if any one of the 6 months in the monthly average column says Alert, then the rolling 6 months would be Alert as well. The data set is about 7000 rows and the percentage of alerts are minimal. I had too many alerts in the example document I attached.

[Melvosh]

This is a formula that will only work after the data is sorted as specified in post #5. It also currently will average 6 months or less, if there are not 6 months that meet the criteria. I will work on a formula that does not require the data to be sorted. Try this in S2 and drag down:

Please Login or Register  to view this content.

Let me know what issues you run into with this formula.