Average and Round

Posted by Jimmy on October 16, 2000 11:23 AM

I need a formual that will average a range of cells and then round the right side of the decimal to a certain number.

Example A1 = 1 A2 = 2 A2 = 1 The average would be 1.33 and I need it to round to the next nearest .25 which in the case would be 1.50. I need it to round either up or down to .00, .25, .50 or .75 on the right side of the decimal, but not change the left side(whole number).

Posted by Neil on October 16, 2000 12:35 PM

Try =ROUNDUP((A1-INT(A1))/25,2)*25+INT(A1)

Posted by Jimmy on October 16, 2000 1:23 PM

Works great, Thanks, you just made my life alot easier.

Posted by Celia on October 16, 2000 3:58 PM

Can also use MROUND :-

=MROUND(A1,0.25)

Celia

Posted by Neil on October 17, 2000 8:49 PM

Thanks Celia

You just made my life a lot easier too

Posted by Jimmy on October 18, 2000 6:17 AM

I can't get the MRound to work, get #NAME?
But do have another question. I would like to use the average and round function in the same equation, but get a circular reference. This is what I have tried =AVERAGE(A1:C1)+ROUNDUP((D1-INT(D1))/25,2)*25+INT(D1) with this formula in Cell D1. I would like the results of my formuala in D1

Posted by Celia on October 18, 2000 6:49 AM

Jimmy
To use MROUND, you have to install Analysis ToolPak (go to Tools>Add-Ins).
I presume that the data in D1 has been input, so you cannot put your formula in cell D1. A formula cannot include the cell which contains the formula.
Can be done with VBA.
Celia

Posted by Doug on October 18, 2000 6:51 AM

For MROUND, you need the Analysis ToolPak. Check Tools, Add-Ins and make sure the Analysis ToolPak is checked.

Posted by Jimmy on October 18, 2000 8:14 AM

Thanks Celia, I do have the MROUND working and I think I have the problem fixed now. I am inputting data into A1, B1, and C1 with the results of the Formula in D1. D1 does not have data input to it just the results of the formula. I have redone your formula a little bit and added the average function inside the MROUND function and it seems to be working alright now. This is the formula I am using - =MROUND(AVERAGE(A1:C1),0.25), This formula is in D1, with my input data in A1-C1. It appears, so far, to be giving me the right answers.

Posted by Jimmy on October 18, 2000 8:28 AM