If Formula help please (97)

Topic

New Reply

I have a formula I need to add another scenario to.

=IF(C20=”TOIL”, C9+C12+C15+C18, C9+C12+C15+C18+C21)

To this I want to add

If C20 is “OTP” then subtract the contents of C21 from P27

Is this possible?

Regards

Kerry

Reply | Quote

Replies

Try
=IF(C20=”TOIL”, C9+C12+C15+C18,if(C20=”OTP”,P27-C21, C9+C12+C15+C18+C21))

HTH
Peter

Reply | Quote

Woops – the Cell P27 is cell with a formula ( =P25+P26 ) causing a circular reference.

I have no idea how to get around this one.

Could the formula all be put in P27 instead?

Kerry

Reply | Quote

Sorry but without a bit more information of the layout of your sheet I have no idea of wheat you could do.
Perhaps you could attach a copy of it, if it is not sensitive?

Reply | Quote

Bat17’s formula is not the cause of your circular reference, there must be some other reference loop, so you need to track it down or post it.

If P27 isn’t referenced anywhere else, you can you can move it into the formula like this condensed version:

=IF(C20=”OTP”,SUM(P25,P26,-C21),(C20″TOIL”)*C21+SUM(C9,C12,C15,C18))

Since we don’t have the whole picture, test this to see that it gives the intended result.

Reply | Quote

=(C20=”TOIL”)*(SUM(C9,C12,C15,C18))+(C20=”OTP”)*(SUM(P27,-C21))

Is this what you’re looking for?

Reply | Quote

Thanks for all of your suggestions.

I need time to absorb this info and give it a go.

If I have no success I will post my spreadsheet for you to look at.

Thanks all.

Kerry

Reply | Quote