function that can search within cell formula, not cell value - Page 2
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: function that can search within cell formula, not cell value

  1. #11
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

     
    [quote]
    On 2002-06-03 14:46, patshan wrote:
    On 2002-06-03 14:43, patshan wrote:
    What a great site. I am an advanced beginner and I am having trouble taking the results of a formula and rounding the number down to the nearest hundred. For instance, 1,333 would be rounded down to 1,300 and 526 would be rounded to 500. I use the paste function but keep getting the circular reference. Any help would be greatly appreciated to this self taught user.
    Steve
    PS
    =$L$103/A12 This is a copy of the formula in question. As I said, I am an advanced beginner. Sorry for the tagging onto the original question, however I do not see a post button.
    Use New Topic to start a new thread/topic.

    Have a look at the FLOOR worksheet function:

    =FLOOR(A1,100)

    or

    =FLOOR(your-formula,100)

    Aladin

  2. #12
    New Member
    Join Date
    Jun 2002
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    [quote]
    On 2002-06-03 14:59, Aladin Akyurek wrote:
    [quote]
    On 2002-06-03 14:46, patshan wrote:
    On 2002-06-03 14:43, patshan wrote:
    What a great site. I am an advanced beginner and I am having trouble taking the results of a formula and rounding the number down to the nearest hundred. For instance, 1,333 would be rounded down to 1,300 and 526 would be rounded to 500. I use the paste function but keep getting the circular reference. Any help would be greatly appreciated to this self taught user.
    Steve
    PS
    =$L$103/A12 This is a copy of the formula in question. As I said, I am an advanced beginner. Sorry for the tagging onto the original question, however I do not see a post button.
    Use New Topic to start a new thread/topic.

    Have a look at the FLOOR worksheet function:

    =FLOOR(A1,100)

    or

    =FLOOR(your-formula,100)

    Aladin


    Aladin
    Thank you for the quick response. Something I did not anticipate. When I round down to the nearest 100 I paint with too broad a brush. Is it possible to discriminate with above 50 round up and below 50 round down?
    Steve

  3. #13
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,772
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

    Something I did not anticipate. When I round down to the nearest 100 I paint with too broad a brush. Is it possible to discriminate with above 50 round up and below 50 round down?

    Steve,

    =IF(A1/100-INT(A1/100)>=0.5,CEILING(A1,50),FLOOR(A1,50))

    is what you want to do?

    Addendum: Substitute "($L$103/A12)" for "A1" in the above formula.

    Aladin




    [ This Message was edited by: Aladin Akyurek on 2002-07-17 23:52 ]

  4. #14
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    First off, the post button is right next to the reply button.

    Secondly, you can use the following formula:
    =FLOOR($L$103/A12,100)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com