+ Reply to Thread
Results 1 to 7 of 7

Formula Help

  1. #1
    Registered User
    Join Date
    04-12-2012
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2016
    Posts
    22

    Formula Help

    Hello - I am trying to write a formula and can not figure it out.

    Based on a number in a cell, have another cell to display the number of runs based on the number. Each run equals 300,000

    This is what I had so far:

    =IF(P14="","",MIN(100,IF(P14<300000,0,IF(P14<300000,1,INT)))& " Runs")

    Not really sure how close I am, I was goign off an old formula.

    0-299,999 display "0 Runs"
    300,000-599,999 display "1 Run"
    600,000-899,999 display "2 Runs".....ect. Up to maybe 100 runs or so.

    thank you so much!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 for Mac OSX & Windows
    Posts
    6,689

    Re: Formula Help

    i would probably just use a lookup array to do that
    so in a table put

    0 0
    300000 1
    600000 2
    900000 3
    etc

    then use that as a lookup

    assuming you have the table in a different sheet say sheet2 columnA andB

    =IF(P14="","", vlookup(P14,sheet2!A:B,2,true)& " Runs")

    see attached example
    Attached Files Attached Files
    Last edited by etaf; 07-16-2013 at 10:39 AM.
    Wayne

    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    04-12-2012
    Location
    Oshkosh, WI
    MS-Off Ver
    Excel 2016
    Posts
    22

    Re: Formula Help

    I'm not a big fan of tables because then I have to find a spot for it and I do'nt want to confuse others that use the table

    I know I had a simple formula before, can anyone work off my formula??

    THanks again!

  4. #4
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula Help

    Simply

    =INT(a1/300000)&" Runs"
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Registered User
    Join Date
    07-13-2013
    Location
    Great Britain
    MS-Off Ver
    Excel 2003 at home, 2007 at work
    Posts
    11

    Re: Formula Help

    or try =ROUNDUP(P14/300000,0).

  6. #6
    Registered User
    Join Date
    07-13-2013
    Location
    Great Britain
    MS-Off Ver
    Excel 2003 at home, 2007 at work
    Posts
    11

    Re: Formula Help

    Sorry. Just realised a glaring error in my logic (0-299999). Despite trying different things ACE_XL's solution above is as good as it gets.
    Last edited by muckleshed; 07-16-2013 at 01:55 PM. Reason: Add comment about solution

  7. #7
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,162

    Re: Formula Help

    kenoble,

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ 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: 6
    Last Post: 10-08-2009, 03:45 AM

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