1. ## If Formula help please (97)

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 <img src=/S/flags/Australia.gif border=0 alt=Australia width=30 height=18>

2. ## Re: If Formula help please (97)

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

HTH
Peter

3. ## Re: If Formula help please (97)

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?

4. ## Re: If Formula help please (97)

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?

5. ## Re: If Formula help please (97)

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

Is this what you're looking for?

6. ## Re: If Formula help please (97)

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.

7. ## Re: If Formula help please (97)

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•