If cell contains text or number copy different cells

[krisjanczuk]

Hi

I am trying to work out the formula I would need for the same cell to copy the value from another cell if the text "PTI" is input but if a value is put in the same cell instead of "PTI" to then multiply that value by the labour rate I have put in a different cell

This is what I have put, its working for calculating the hours but when I put PTI in the cell it comes up with a value error
=IF(A7>=0,A7*B5)*OR(A7="PTI",B4)


Cost Allocations.xls

[FDibbins]

Hi, welcome to the forum

you were almost there...
=IF(A7="PTI",B4,A7*B5)

[krisjanczuk]

Awesome ! Thanks

But now if I drag the formula down it moves the cell down (so b4 turns to B5)

How do I make that stay B4 so that when ever PTI is selected it always copies the value from B4?

=IF(A7="PTI",B4,A7*B5)

[krisjanczuk]

all good sorted that last bit

How would I make the cell blank if the total was zero (not yet filled in)

[FDibbins]

Maybe this?
=IF(A7="","",IF(A7="PTI",$B$4,A7*B5))

[krisjanczuk]

Champion !!

Thanks heaps !!

[FDibbins]

Happy to help