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!
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
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.
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!
Simply
=INT(a1/300000)&" Runs"
Life's a spreadsheet, Excel!
Say thanks, Click *
or try =ROUNDUP(P14/300000,0).
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
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]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks