If between and help?

[Nathan201]

Hi all,

I am trying to create a formula for sizing pipe using IF. But im not sure if its the right way to go for this formula. The parameters are listed below, but essentially I want to be able to input a flow rate (A1), and IF the flow rate is between say 0.307 and 0.650 then the pipe size would be 32 (A2). Im no excel wizard but I came somewhere close yesterday and now cant figure it out. I was going down the path of =IF(AND(E10>=P8,O9,O8),IF(E10>=P9,O10,O9)) which kind of works, but if I continue and add more to it, it fails.

Pipe Size Max Flow
20 0.159
25 0.307
32 0.650
40 0.982
50 1.923
65 3.200
80 5.518
100 11.391
125 20.682
150 33.636
200 69.455

Any help would be wonderful.

Thanks

Nathan.

[cbatrody]

Hi Nathan,

Welcome to the Forum.

You would get better help if you attach a sample workbook with enough data to demonstrate your requirement. Make sure your desired results are shown, mock them up manually if necessary. Remember to desensitize the file by removing all confidential information before upload!

See the following URL for help on how to upload a file.
http://www.excelforum.com/members/da...ch-a-file.html

[Nathan201]

Thanks,

Sorry I haven't introduced myself, im afraid I cant bring much to the table in terms of help, only problems for you amazing people to solve

I have attached a mock up of what I am trying to achieve. I have done it in a separate workbook as the original is quite a large file with various formulas in. Hopefully it makes sense to you all.

Thanks again

Nathan.

[millz]

Not the most glamorous formula, but works:

Please Login or Register  to view this content.

Put it in cell B6 and copy down.

[Nathan201]

Absolutely amazing!!! Thank you very much! that has made me so happy!!!

:D:D:D:D:D

THANKS!

[Phuocam]

Try this in B6:

=INDEX($I$6:$I$16,MATCH(TRUE,$J$6:$J$16>=A6,0))

Enter with Ctrl+Shift+Enter.

[cbatrody]

OR,

try the following: a simple LOOKUP formula.

=LOOKUP(A6,J$6:J$17,I$6:I$17)

I have slightly modified your diameter table to suit this formula. See the attached file.

[Nathan201]

Thanks guys!

[cbatrody]

You are welcome

If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.

Also, you can directly thank those who have helped you by clicking on the small * (star) icon located in the lower left corner of a post that you have found to be helpful.