If greater than..

[Moxy1980]

I have the following problem...

I have a system of pipework, made from individual pipes. Each pipe have 2 flanges, 8 bolts, and 8 nuts. I have the prices for these, and a very very simple forum whereby if I type in an amount for 'Number of meter's, I can get a price (cost of pipe x amount of meters + flanges + bolts + nuts).

Now the maximum length I am allowed for a single pipe is 5m's. Therefore I actually need a formula that if I type in more than 5 meters then it will multiply my flanges, bolts and nuts, by two and add to my pipe price. If its more than 10 meters, it muliplies them by 3 and add to my pipe price. If its more than 15 mters its mulitples them by 4 and add to my pipe price, etc, etc, etc.

So I think it need some that says 'if A is greater than B, then do C. If X is greater than B, then do C'....

Any help appreciated,

John C

[JBeaucaire]

Please post up your sample workbook showing the exact layout of your current sheet, we need to see your prices, your entry cell for length of pipe...and perhaps a few "sample results" you've filled in manually so we'll know if we get the formula right.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

[JBeaucaire]

The formula would be two formulas added together, we just need to know where your values are. For instance:

=(E7*F4) + (SUMPRODUCT($G$3:$I$3 * $G$4:$I$4) * (INT(E7/5)+1))

This matches the attached sample sheet.

The first formula is the pipe length x pipe per meter price.
The second half is the # of pieces of flanges/nuts/bolts x prices of flanges/nuts/bolts x pipe meters/5 rounded up.

[Moxy1980]

Ok, firstly thanks for the direction.

You can see on my sheet that I need the accessories to be added once if the value entered is greater than 5 but less than 10, twice if greater than 10 but less than 15, three times if greater 20 but less than 25...and so on...

Thanks for this

JC

[Moxy1980]

The formula would be two formulas added together, we just need to know where your values are. For instance:

=(E7*F4) + (SUMPRODUCT($G$3:$I$3 * $G$4:$I$4) * (INT(E7/5)+1))

This matches the attached sample sheet.

The first formula is the pipe length x pipe per meter price.
The second half is the # of pieces of flanges/nuts/bolts x prices of flanges/nuts/bolts x pipe meters/5 rounded up.

So, from that if it goes over 5 then that means 2 sets of flanges/nuts/bolts are added, but i need three sets added after 10, 4 sets after 15, etc, so how do I do that?

I have a very basic knowledge of excel. Can you tell me why you have $ signs in the no of pieces of flanges/nuts/bolts?

[JBeaucaire]

$ signs make the cell references absolute. If the cell gets copied, cell references adjust themselves for relative positions, which is good because it allows a formula written for data across row1 to be copied down and it will work on each row for that row's data. But sometimes you don't want the cell references to change, they refer to something absolute and unchanging, like # of flanges and flange prices.

[JBeaucaire]

Try this in C23:

=SUM(E8:E17) * (IF(D6=5,1,INT(D6/5)+1))

[Moxy1980]

I'm unsure where you mean; I can see you mean on my original sheet, but I don't know how to link that to the first formula...current sheet attached...

[JBeaucaire]

In C22...use this, it takes into account the fact that sometimes you select a maximum pipe length (5, 10, 15, etc).

=(C3*C6) + (SUMPRODUCT($D$8:$D$17 * $C$8:$C$17) * (INT(C3/5)+(MOD(C3,5)<>0)))