+ Reply to Thread
Results 1 to 10 of 10

Analyze number to specific results

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Analyze number to specific results

    Hello everyone
    I have a number in Range("D6") which is 57439.25

    I need to analyze the number in range("G6:G9") so as to get the results as following :
    50000 In G6
    7000 In G7
    400 In G8
    39 In G9

    and ignore that part 0.25

    If possible I prefer UDF function to do that task
    Thanks advanced
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Analyze number to specific results

    This
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    will return you:

    Please Login or Register  to view this content.

    It can be fixed to get 39 instead of 30 and 9 but this would be simplest.
    Never use Merged Cells in Excel

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Analyze number to specific results

    Why do you prefer a UDF when a normal function is perfectly adequate

    G6:
    =ROUNDDOWN($D$6,-4)

    G7:
    =ROUNDDOWN($D$6,-3)-G6

    G8:
    =ROUNDDOWN($D$6,-2)-H6-G6

    G9:
    =ROUND(D6-SUM(G6:I6),0)
    Last edited by Richard Buttrey; 05-30-2015 at 01:19 PM.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Analyze number to specific results

    Thank you very much for both of you
    I tested your suggestions but I didn't get the expected results

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Analyze number to specific results

    See the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Analyze number to specific results

    Thank you Mr. oeldere
    It's working well

    I'm searching for UDF function to do that task .. Is that possible ?
    Another point I used Int function in your formula so as to get the integer number without fractions ..

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Analyze number to specific results

    Here, try this:

    Please Login or Register  to view this content.
    Use it as:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Analyze number to specific results

    Thank you very much Mr zbor for this nice udf
    I tested it but I got incorrect results
    I will attach a file again with the expected resutls
    Attached Files Attached Files

  9. #9
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    16,040

    Re: Analyze number to specific results

    Try now:

    Please Login or Register  to view this content.
    Last edited by zbor; 05-30-2015 at 03:33 PM.

  10. #10
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Analyze number to specific results

    Yes Yes Mr. zbor
    That's exactly what I need .. You are all great people
    Thank you very much for this wonderful UDF function

+ 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. Replies: 2
    Last Post: 12-17-2013, 06:30 AM
  2. [SOLVED] how to stop Command button If specific number is entered in a specific cell range
    By sspreyer in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2013, 05:10 AM
  3. Replies: 2
    Last Post: 07-13-2011, 11:11 AM
  4. Using LOOPS with IF to analyze specific cell ranges
    By seaottr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-11-2010, 03:10 AM
  5. How do I import text file, analyze data, export results, open next file
    By Geoffro in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2005, 04:06 PM

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