Solved: If statement

[Hoopsah]

Hi,

I have attached a copy of the spreadsheet I am trying to work out a calculation on.

I want to calculate the band width of a pipe given certain criteria.

=IF($C$20<C37,C37-$C$20,0)

Works and gives me the correct result, however, want I want to incorporate is a further condition, so that the fomulas will say:

If C20 < C37, then, C37 - C20, but, If C20 is less than C37 then no other calculations are necessary as it will always be smaller than the other options.

So, if the calculation gives a result then no other calculations are required and should state zero.

Hope this makes sense, any help appreciated,

Cheers

Hoopsah

[Hoopsah]

Quick amendment to above,

I think all I want is:

If C20 < C39 then C39 - C20 else 0

To also incorporate If D37 > 0 then 0 else D20 < C39 then D39 -C20 else 0

If in the cells with the formula a positive result has been gained, it will be greater than zero so all the other cells can state zero, or else they do the calculation.......


Hope this makes a bit more sense

[Bob Phillips]

How about

=IF(AND(COUNTIF($D$27:$D27,">0")=0,$C$20<$C37),$C37-$C$20,0)

[Hoopsah]

Thanks Bob,

It still completes the calculation even though it has found a result in the line above, I have tried:

=IF(D36>0,0,IF($C$20<C37,C37-$C$20,0))

Which does work, but not to sure how strong it is.

[Hoopsah]

Nope,

Mine only works for the line above, so every other line is correct.

0
250
0
1250
0
2250


???

[Hoopsah]

Sorry Bob,

I was to busy looking at mine and not updating your code to incorporate the correct cells.

Mines works as long as you are only checking 2 lines,

Yours, after a bit of inspection, works perfectly.

Sorry for not getting it straight away, it works better and is more robust than mine,

Thanks for your help Bob

Gerry

[Bob Phillips]

It worked here Gerry