loveland517
New Member
- Joined
- Jan 17, 2022
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
The following formula returns "#div/0"
=AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(0,0,0),FILTERED!H:H,"<"&TIME(7,0,0),FILTERED!O:O,">8")+AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(23,0,0),FILTERED!H:H,"<"&TIME(24,0,0),FILTERED!O:O,">8")
I understand the cause is due to the first argument in the formula...
=AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(0,0,0),FILTERED!H:H,"<"&TIME(7,0,0),FILTERED!O:O,">8")
...having no results returned (BACKGROUND: in the "FILTERED" sheet there are is no data to average in the O column that occurred from midnight to 7am that took over 8 hours)
How use I (within the same formula) express that it should just use "0" if the AVERAGEIFS formula returns "#div/0"?
=AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(0,0,0),FILTERED!H:H,"<"&TIME(7,0,0),FILTERED!O:O,">8")+AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(23,0,0),FILTERED!H:H,"<"&TIME(24,0,0),FILTERED!O:O,">8")
I understand the cause is due to the first argument in the formula...
=AVERAGEIFS(FILTERED!O:O,FILTERED!H:H,">="&TIME(0,0,0),FILTERED!H:H,"<"&TIME(7,0,0),FILTERED!O:O,">8")
...having no results returned (BACKGROUND: in the "FILTERED" sheet there are is no data to average in the O column that occurred from midnight to 7am that took over 8 hours)
How use I (within the same formula) express that it should just use "0" if the AVERAGEIFS formula returns "#div/0"?