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)


    Any help please

    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!

    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!

Participate now!

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

Register Yourself Login