I have excel 2016.

I have two values that come out of some formulas that I want to compare to a table and output either "pipe size is ok" or "pipe size is too small".
One of the values I get is for maxed developed (dev) length and that has to be rounded up to the next larger table value. Then using that column in the table, I take the number of fixture units I have and reference that against both the pipe size and the max dev length. If the number of fixture units is larger than the max allowed for that pipe size, given the max developed length allowed then the pipe would need to be upsized. if the number of fixture units is less than allowed then the pipe size is ok.

my table basically looks like this:

distro pipe 40 60 80 100 ... <=max dev length (ft)
1/2" 2.5 2 1.5 1.5 ... <= max fixture units allowed
3/4" 9.5 7.5 6.5 5.5 ...
1" 32 25 20 16.5 ...
1-1/4" 32 32 27 21 ...
1-1/4" 32 32 32 32 ...
1-1/4" 80 80 80 75 ...
1-1/2" 87 87 87 87 ...
... ....

so given my other calculations I get a maxed developed length 49 ft for a 3/4" pipe and total fixture count of 56. my result should output "pipe size is too small"

My other issue comes in that I have multiple instances of the same value in both the meter and distro pipe columns. therefore I need an interative formula to find the value that is larger than the number of fixture units that I come up with.

I have used an index match function to round the max developed length up to the next higher column value however I cannot figure out a formula to then reference the fixture unit values.

=INDEX('pipe sizing charts'!$C$2:$M$2,MATCH($H$59,'pipe sizing charts'!$C$2:$M$2,1)+(LOOKUP($H$59,'pipe sizing charts'!$C$2:$M$2)<>$H$59))

where C2:CM are my max dev length table numbers and H59 is the max dev length of the section I am sizing

  • 1
    distro pipe, 40,60,80,100 are the headers? 1/2", 2.5,2,1.5,1.5 refers to max fixture units? How does this work in a table? Columbs should be the same from top to bottom. I'm not a plumber so these words don't mean much. It's the Max Dev length and then Max Fixture Units that is confusing. It seems 1/2" is very short, but 1-1/2" is very long? It's confusing to a person that isn't a plumber. I think you have to dumb it down some for people that work in technology, but have zero plumbing experience. – ejbytes May 7 '17 at 9:25

I think I have it.

My assumptions from your question:

  • Your INDEX formula finds the correct column
  • You need to go down that column to find the value one step higher than the value you calculate
  • You already have the value you are looking for (in the case of your question: 56)
  • You need to return the data from column B (I think this is the reference of your first column)

So, if my assumptions are correct, you first need to sort the data in the table in descending order.

You then need to return the column letter of the column that you are wanting to search down. Wrapping your INDEX formula in a SUBSTITUTE/ADDRESS function should work

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX('pipe sizing charts'!$C$2:$M$2,MATCH($H$59,'pipe sizing charts'!$C$2:$M$2,1)+(LOOKUP($H$59,'pipe sizing charts'!$C$2:$M$2)<>$H$59))),4),"1","")

Now that you have the column letter, you can use INDIRECT to write your INDEX/MATCH

=INDEX('pipe sizing charts'!B:B,MATCH($H$59,INDIRECT(xx&":"&xx),-1))

The xx in the above formula is the cell reference where you entered the SUBSTITUTE formula above. MATCH works in this case as the "-1" match type looks for the next number greater than the one you are looking for, but the data has to be sorted in descending order for it to work.

The above should do it, with maybe some minor tweaks to fit it into your sheet.

Let me know how you go.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.