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