AVERAGEIF Function

Hollie Green

There was some errors in the commas. Note if you don't have any data in the range to Average it will give a # divide by 0 error if you want to avoid that and have it just show up as blank use the bottom formula

=IF(INDEX(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31))), 1) < 1, AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))), AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2)

=IFERROR(IF(INDEX(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31))), 1) < 1, AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))), AVG(COLLECT({Test 2023 Days 3F+P Completion}, {Test 2023 Duplicate}, 1, {Test 2023 Receive Date}, AND(@cell >= DATE(2023, 1, 1), @cell <= DATE(2023, 3, 31)))) / 2), "")

RaffyM

@Hollie Green, thank you for your persistence, it works perfectly now. I'm glad you help.

One additional help if you may. How to get rid of - and make it absolute value? What to add in my formula?

=IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row = "Requires 3F+P", NETWORKDAYS([Inspection Due Date]@row, [3F+P Completion Date]@row)))

Thank you 😉

Hollie Green

You would just add ABS formula

=ABS(IF(ISBLANK([3F+P Result]@row), "", IF([Parts Disposition]@row = "Requires 3F+P", NETWORKDAYS([Inspection Due Date]@row, [3F+P Completion Date]@row))))

RaffyM

Oh, poor me 😂. I didn't know that ABS is the absolute value. Maybe because I haven't use a lot of different functions in my work sheet. Thank you again for all the help. 😊