Sum prices 4 object made up of varying number of components

[Dan]

This is for drainage pit. I have been trying to incorporate IF and
sumproduct(-- statements and I can't seem to get it right. (Maybe I need to
write a program?)

I have a large number of pits which will be made up of a combination of a
base piece and a number of riser components to make up the height required.
The minimum width of pit is determined by the largest pipe to it, according
to; Pipe Dia<=450 then min. width=670 and Pipe Dia>450 then min.
width=670+200(mm).

Prices are for pieces in standard sizes, as follows.
750 (pit width):
Base=900mm(depth)=$a
Risers=600mm=$b
Risers=300mm=$c
Risers=150mm=$d

900 (pit width):
Base=1200(depth)=$w
Risers=600mm=$x
Risers=300mm=$y
Risers=150mm=$z
etc.

All bases are either 900 or 1200 deep.
All pits will require a base and at least 1 riser (as all pit depths greater
than 1200mm (presumably easier then to leave out base in pit height calc)
Spreadsheet has been set up accordingly.

Eg. Pit depth = 1600mm and largest pipe = 500mm.
Therefore, need:
- pit with greater than 700mm so use 750 pit.
- 1 base+2 risers (900+600+1500=1650)
- output price (one pit) = $(a+b+d)

I hope this makes sense, let me know if you need any more info.
I know this is longwinded, but any help would be greatly appreciated; I've
seen the answers on this site and I'm sure one of you can do this!

 

[Roger Govier]

Hi Dan

Here is one solution.
Create 2 tables (I used A1:B5 and D1:E5 - but they could be on a separate
sheet if required)
List1
0
150 75
300 120
600 180
900 250

i.e make them in ascending size, but start with 0
Do the same for List2 but obviously in each case you your prices.

Insert>Name>Define>
Name List1
Refers to =A1:B5
Name List2
Refers to =D1:E5

Put your inputs for Depth and Pipe size in A11 and B11 respectively
Insert>name>Define>
Name Depth
Refers to =$A$11
Name Diameter
Refers to =$B$11
Name uselist
Refers to =IF diameter>450,List2,List1)

In A15 enter
=IF(CEILING(depth,150)-SUM($A$14:$A14)>0,
VLOOKUP(CEILING(depth,150)-SUM($A$14:$A14),uselist,COLUMN()),"")
Copy across to B15
Copy A15:B15 down through cells A16:A25

The Total cost is =SUM(B15:B20)

You could have the 2 lists and the calculation section on another page, just
change the cell references in the Named ranges to match.