I'm using the following formula to get a 7-day average value
SUM(IFERROR(QUERY(B2:B500, "limit 7 offset " & COUNTA(B2:B500)-7))) / 7
If I wrap that in FLOOR the value changes wildly:
FLOOR(SUM(IFERROR(QUERY(B2:B500, "limit 7 offset " & COUNTA(B2:B500)-7))) / 7, 1.1)
I'm assuming it's because it is flooring all the values used in the sum, as opposed to the final result of the sum function?
Is there are a way to make it resolve the sum and then floor the result? Or whatever else it is that's wrong with my formula
Make sure the second parameter to floor() is correct. You are probably looking for 0.1 rather that 1.1. Compare:
=floor(9.55, 0.1) → 9.5
=floor(9.55, 1.1) → 8.8