Help with Excel Formulas

[Kbass]

I am creating a spreadsheet to help me quote electrical underground work. The spreadsheet allows me to enter information about all of the electrical conduit that will be in the ground (length, depth, pipe size, etc.), which is all important information and needed for accurate pricing. The problem I can't seem to figure out is that multiple rows can be in the same trench so I only need to use the longest run (all others will use same hole), but I need most of the other information in those rows. If anyone can help me with a formula that can look at matching values in "Trench Number" column, then take highest value in "Trench/Hole Length" column and delete or zero out any other values with same "Trench Number". If you can offer any help, I greatly appreciate it!

Area Trench Number Trench/Hole Length Depth-Inches

I have attached a sample spreadsheet for review.

[Bernie Deitrick]

I would use a column of formulas to find the longest run for each trench - here is a working column of formulas:

=MAX(IF(B:B=B3,C:C))

There are array formulas that are entered using Ctrl-Shift-Enter

[protonLeah]

Named Ranges:

CostPerFoot: =Sheet1!$F$2:$F$41
Depth: =Sheet1!$E$2:$E$41
PricingLength: =Sheet1!$D$2:$D$41
TrenchHours: =Sheet1!$H$2:$H$41
TrenchLength: =Sheet1!$C$2:$C$41
TrenchNumber: =Sheet1!$B$2:$B$41
TrenchTotal: =Sheet1!$G$2:$G$41
WorkArea: =Sheet1!$A$2:$A$41

Replace cost/ft
=IF(E4=$AC$3,$AD$3,IF(E4=$AC$4,$AD$4,IF(E4=$AC$5,$AD$5,IF(E4=$AC$6,$AD$6,IF(E4=$AC$7,$AD$7,0)))))
with:

Please Login or Register  to view this content.

max lenght per trench using array formula in inserted helper column D:

Please Login or Register  to view this content.

I don't know how you will calculate trench total, either trench/hole length (col c) * col f, or, pricing length (helper col d) * col f?

[Kbass]

Wow! I really appreciate the help! Thank you very much! I will try these tonight.

[jeffreybrown]

Hello Kbass & Welcome to the Forum,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #1 about proper thread titles and adjust accordingly...