# Rounding and math formulas in Excel and Google Sheets

> Round to any step, round up and down, weighted averages, ranks and remainders. 6 answers, each checked by running it.

Source: https://spreadsheetpractice.com/formulas/topic/math · Updated October 29, 2026 · Formulas and results on this page were run by the Spreadsheet Practice formula engine on October 29, 2026

## Answers

- [How to round to the nearest 100 or 1000 in Excel](https://spreadsheetpractice.com/formulas/round-to-the-nearest-hundred): Use ROUND with a negative number of digits: =ROUND(A2,-2) rounds to the nearest 100, and -3 to the nearest 1000. `=ROUND(A2,-2)`
- [How to always round up in Excel](https://spreadsheetpractice.com/formulas/always-round-up): Use =ROUNDUP(value,0) to round up to the next whole number, or =CEILING(value,step) to round up to a multiple. `=ROUNDUP(B2/C2,0)`
- [How to calculate a weighted average in Excel](https://spreadsheetpractice.com/formulas/weighted-average): Multiply each value by its weight, add them up and divide by the total weight: =SUMPRODUCT(values,weights)/SUM(weights). `=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)`
- [How to rank numbers in Excel](https://spreadsheetpractice.com/formulas/rank-values): Use =RANK.EQ(value, list) for largest = 1, adding 1 as the third argument for smallest = 1. Lock the list with $. `=RANK.EQ(B2,$B$2:$B$7)`
- [How to calculate an average without zeros in Excel](https://spreadsheetpractice.com/formulas/average-excluding-zeros): Use =AVERAGEIF(range,"<>0"). It averages only the cells that are not zero. `=AVERAGEIF(B2:B7,"<>0")`
- [How to multiply a column by the same number in Excel](https://spreadsheetpractice.com/formulas/multiply-a-column-by-a-number): Put the number in a cell and lock it with $: =B2*$E$1, then fill down. `=B2*$F$1`

More topics: [Counting formulas](https://spreadsheetpractice.com/formulas/topic/count) · [Sum formulas](https://spreadsheetpractice.com/formulas/topic/sum) · [Lookup formulas](https://spreadsheetpractice.com/formulas/topic/lookup) · [IF and logic formulas](https://spreadsheetpractice.com/formulas/topic/logic) · [Text formulas](https://spreadsheetpractice.com/formulas/topic/text) · [Date formulas](https://spreadsheetpractice.com/formulas/topic/date) · [Dynamic array formulas](https://spreadsheetpractice.com/formulas/topic/dynamic) · [Duplicates and data cleanup formulas](https://spreadsheetpractice.com/formulas/topic/cleanup) · [Percentage formulas](https://spreadsheetpractice.com/formulas/topic/percent)

Every answer: [Formula answers](https://spreadsheetpractice.com/formulas). Formulas used: `ROUND`, `ROUNDUP`, `SUMPRODUCT`, `SUM`, `RANK.EQ`, `AVERAGEIF`

---

Spreadsheet Practice (https://spreadsheetpractice.com) teaches spreadsheet formulas by practice: every exercise is graded in a live grid and re-checked on changed data.