# Formula Help

1. ## 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. ## 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

3. ## 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. ## Re: Formula Help

Simply

=INT(a1/300000)&" Runs"

5. ## Re: Formula Help

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

6. ## 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.

7. ## 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.

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.

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

#### 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