+ Reply to Thread
Results 1 to 9 of 9

Product and Round a series of numbers

  1. #1
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Product and Round a series of numbers

    Hi All,

    I have to do this kind of calculation quite often and i would like to know if there is a shortcut out there somewhere. The scenario is that i would like to multiply 2 numbers together then round to the nearest penny, then multiply that value by another number and round to the nearest penny, and continue this pattern on for a set amount of numbers. Mind you the end value will sometimes be different than just multiplying all of the numbers together then rounding at the end. Here is an example:

    Column A
    1.19
    1.11
    1.86
    1.47
    1.36
    1.09
    1.78
    1.02
    1.20

    If you were to do this formula round(product(A2:A10),2) you would come up with 11.66, but i would like the value that is calculated from this formula:
    =ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(ROUND(A2*A3,2)*A4,2)*A5,2)*A6,2)*A7,2)*A8,2)*A9,2)*A10,2) which would be 11.68

    This number can be significatly different if the numbers that you are looking at have more than two decimal places. I am looking for a formula, if at all possible, that will do what i have shown just must shorter. Thanks in advance for any assistance.
    Last edited by amotto11; 08-20-2013 at 02:23 PM.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Product and Round a series of numbers

    One way would be to use a helper column.
    With the data above in A1:A10, put =ROUND((A2*A1),2) in cell B2 and =ROUND((A3*B2),2) in cell B3 and then copy B3 down.

  3. #3
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Product and Round a series of numbers

    Thanks for the suggestion, but that is another work around which i have done in the past. There may be no formula in excel to do what i am asking, but i thought i would put it out there since i am computing this value often.

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Product and Round a series of numbers

    There's always a way... Maybe you'll need a UDF for it, but that would take a far cleverer man than I.

    Good luck in finding your solution.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Product and Round a series of numbers

    Nevermind...

  6. #6
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Product and Round a series of numbers

    Badly,

    Thank you for your help, a UDF might be my way i will have to go, I appretiate your input. If anyone else knows of an excel function or has a UDF that can satisfy my requirments i would greatly appretiate it.
    Last edited by amotto11; 08-20-2013 at 02:46 PM.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Product and Round a series of numbers

    UDF?

    Please Login or Register  to view this content.
    e.g., =MyProduct(A2:A10, 2)
    Entia non sunt multiplicanda sine necessitate

  8. #8
    Forum Contributor
    Join Date
    08-22-2011
    Location
    Texas, United States
    MS-Off Ver
    Excel 2007 and 2010
    Posts
    516

    Re: Product and Round a series of numbers

    shg,

    That worked great. Thanks for the help

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Product and Round a series of numbers

    Heres a UDF

    Please Login or Register  to view this content.

    Used like
    =MyRound(A2:A10)

    Or an optional 2nd argument for the # of digits to round by (defaults to 2 if omitted)
    =MyRound(A2:A10,4) - if you wanted to round each step to 4 digits.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Random Series of Numbers - Each series to have same GEOMEAN
    By nbezza in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-11-2013, 12:51 AM
  2. Using Product to convert Text(Numbers) to Numbers
    By flebber in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2010, 10:30 AM
  3. Xl 2007 VBA applying round dot format to chart series
    By Richard Buttrey in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 08-14-2009, 12:21 PM
  4. How do you round off numbers 5,518,943 to 5,520,000
    By laurn in forum Excel General
    Replies: 1
    Last Post: 08-16-2006, 10:35 AM
  5. Replies: 3
    Last Post: 04-20-2005, 05:06 PM

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