Search code examples
google-sheetssumfloor

Use FLOOR on SUM's result

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

Solution

  • 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