How to first average a range and then round the value
Requirement:
The user needs to average cells A1:A10, then round up, but to round up when the number is greater than or equal to ".6" or to round down when the number is less than ".6"
i.e.
Cell calculation =< 4.5 (result is 4)
or
Cell calculation => 4.6 (result is 5)
Solution:
=IF(MOD(AVERAGE(A1:A10),1)>0.5,ROUNDUP(AVERAGE(A1:A10),0),ROUNDDOWN(AVERAGE(A1:A10),0))
To make the result show blank " " if there is no data in the range enter the following formula:
=IFERROR(IF(MOD(AVERAGE(A1:A10),1)>0.5,ROUNDUP(AVERAGE(A1:A10),0),ROUNDDOWN(AVERAGE(A1:A10),0)),0)
Obtained from the OzGrid Help Forum.
Solution provided by NBVC.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use IFERROR, ROUND and SUM in one formula |
| How to round to nearest .25 (times) |
| How to create a macro to round total based on a cell value |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
