+ Reply to Thread
Results 1 to 6 of 6

Equalising numbers

Hybrid View

Blakey Equalising numbers 06-08-2011, 05:32 PM
MrShorty Re: Equalising numbers 06-08-2011, 05:43 PM
Blakey Re: Equalising numbers 06-08-2011, 05:46 PM
Colin Legg Re: Equalising numbers 06-08-2011, 05:51 PM
MrShorty Re: Equalising numbers 06-08-2011, 06:25 PM
Colin Legg Re: Equalising numbers 06-08-2011, 06:47 PM
  1. #1
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: Equalising numbers

    I'm sure some of the maths experts on here will provide a solution that absolutely rocks, but in the meantime I think a generic function to calculate each number in the series would be:
    =ROUNDDOWN(AVERAGE(Numbers),0) +(MOD(AVERAGE(Numbers),1)>=(n/COUNT(Numbers)))
    Where n is the position of the 'equalised' number in the equalised set.
    Due to Double precision, 0.33333' does not necessarily equal 1/3, 0.66666' to 2/3 etc., so revise the function for Excel to this:
    =ROUNDDOWN(AVERAGE(Numbers),0) +((1/1000+MOD(AVERAGE(Numbers),1))>=(n/COUNT(Numbers)))
    Where the 1/1000 is a small fraction to ensure that the >= is correctly met. Obviously if your sets became much larger then this would fall short.

    Examples up to 7 random numbers in a set in the attached example. Numeric order in the equalised set and negative averages not taken into consideration. For negative averages I think a function (which no doubt can be simplified) would be:
    =TRUNC(AVERAGE(Numbers))+SIGN(AVERAGE(Numbers))*((1/1000+MOD(ABS(AVERAGE(Numbers)),1))>=(n/COUNT(Numbers)))
    Attached Files Attached Files
    Last edited by Colin Legg; 06-08-2011 at 07:18 PM. Reason: added comment re negative averages
    Hope that helps,

    Colin

    RAD Excel Blog

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1