I have a sample file that contains just 1 formula:

=IF(COUNT(B2:S2)=0,"",IF(COUNT(B2:S2)<5,AVERAGE(B2 :S2),AVERAGE(SMALL(S2:INDEX(B2:S2,LARGE(IF(B2:S2< "",COLUMN(B2:S2)-1),5)),{1,2,3,4}))))

This formula averages the 4 lowest values from the last 5 values in the
range, or, if there are not 5 values, averages what's available.

Whenever I open the file and then attempt to close the file without do
anything whatsoever, I'm prompted to save changes.

I know this subject has been hashed before and I believe the consensus
opinion is that INDEX is not volatile. But, there you go!

Biff