# Average and Round Function

• Hi All,

I am looking to add an average function with a round function but with a twist.
I need 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)

• One way...

=IF(MOD(A1,1)>0.5,ROUNDUP(A1,0),ROUNDDOWN(A1,0))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Hi

Sorry if I misunderstand your post, but how does this average and round the range A1:A10 ?

Thanks

• Sorry, didn't notice the request to average the range first..

just replace each occurance of A1 with AVERAGE(A1:A10)

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Hi

Sorry, still struggling here !

I cannot get this to work.
=IF(MOD(AVERAGE(A1:A10,1)>0.5,ROUNDUP(AVERAGE(A1:A10,0),ROUNDDOWN(AVERAGE(A1:A10,0)

Thanks

• should be:

=IF(MOD(AVERAGE(A1:A10),1)>0.5,ROUNDUP(AVERAGE(A1:A10),0),ROUNDDOWN(AVERAGE(A1:A10),0))

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Nice that works

One last thing, how do I make this show blank " " if there is no data in the range and I get a divide by zero error?

Thanks

• Wrap whole formula in an IFERROR() function:

=IFERROR(IF(MOD(AVERAGE(A1:A10),1)>0.5,ROUNDUP(AVERAGE(A1:A10),0),ROUNDDOWN(AVERAGE(A1:A10),0)),0)

Where there is a will there are many ways. Finding one that works for you is the challenge!

• Awesome, thank you for your help.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!