Nested IF Function

[paulunh11]

Formula below works perfectly fine but I need to add another IF function and not sure how to do that.

In Column S, I have "yes" or "no" in each cells and I need the new formula to say that IF there's a "yes" in S5, then take the formula below. If there's a "no", T5 should be blank.

Any help would be greatly appreciated.


=(IF(T$3<'Input Sheet'!$F53,'Input Sheet'!$E53/13,(IF(T$2>'Input Sheet'!$I53,'Input Sheet'!$K53/13,(IF((AND(T$2>'Input Sheet'!$F53,T$3<'Input Sheet'!$I53)),'Input Sheet'!$H53/13,(IF(AND(T$2<='Input Sheet'!$F53,T$3>='Input Sheet'!$F53),(((TEXT('Input Sheet'!$F53-Period!T$2,$A$1)+1)/28*'Input Sheet'!$E53/13)+(TEXT(T$3-'Input Sheet'!$F53,$A$1)/28*'Input Sheet'!$H53/13)),IF(AND(T$2<='Input Sheet'!$I53,T$3>='Input Sheet'!$I53),(((TEXT('Input Sheet'!$I53-Period!T$2,$A$1)+1)/28*'Input Sheet'!$H53/13)+(TEXT(T$3-'Input Sheet'!$I53,$A$1)/28*'Input Sheet'!$K53/13)),"problem")))))))))

[JeteMc]

Assuming the formula is in T5 try: =IF(S5="No","",IF(T$3<'Input Sheet'!$F53,'Input Sheet'!$E53/13,(IF(T$2>'Input Sheet'!$I53,'Input Sheet'!$K53/13,(IF((AND(T$2>'Input Sheet'!$F53,T$3<'Input Sheet'!$I53)),'Input Sheet'!$H53/13,(IF(AND(T$2<='Input Sheet'!$F53,T$3>='Input Sheet'!$F53),(((TEXT('Input Sheet'!$F53-Period!T$2,$A$1)+1)/28*'Input Sheet'!$E53/13)+(TEXT(T$3-'Input Sheet'!$F53,$A$1)/28*'Input Sheet'!$H53/13)),IF(AND(T$2<='Input Sheet'!$I53,T$3>='Input Sheet'!$I53),(((TEXT('Input Sheet'!$I53-Period!T$2,$A$1)+1)/28*'Input Sheet'!$H53/13)+(TEXT(T$3-'Input Sheet'!$I53,$A$1)/28*'Input Sheet'!$K53/13)),"problem")))))))))
Let us know if you have any questions.

[paulunh11]

Yes thank you - that worked - that was easy

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[paulunh11]

hi JeteMc.

Any idea why my formula in cell I2 in the Input sheet isn't working.

I also need the contract to be calculated based on the Expiry date column and Extension column in Input Sheet Tab.

If extension is "NO" and contract ends on 2-Mar-18 - it should stop on that date which would be in column L in the period Tab.

If there's a "YES" in extension than it should continue but new price should be adjusted by % in column G in the Input sheet. i.e. if PetroChina has 3% price adjustemnt,
then the new price column H in Input sheet should kick in after expiration date.

I think my formula in the Period tab is wrong as it calculates period breakdown according to dates and it does not match total contract adjustment.

Any help would be great appreciated.

paul

[southward]

Hi,

Regarding the formula in I2 in the Input Sheet. The formula looks good. This issue is the date value in column B. Your dates are preceded with an apostrophe ('). Remove the apostrophe and the formula in I2 works.

Looking at your other inquiries.

Hope this makes sense and is helpful.

Cheers