Hi All,
Can anyone please help with the formula in Period tab?
I deleted the forumula in lines 7&9 as an example.
If a contract has an expiration date and "NO" as an extension as per columns AO and AP in the period tab.
Then I would like the formula to stop as per two highlighted yellow lines (7 & 9 in the period tab)
If a contract has a "yes", then i would like to keep the formula.
so i simply need updating the formula that's currently in the Period tab.
Any help would be much appreciated.
Last edited by AliGW; 03-26-2017 at 12:21 PM.
What is the formula and where should it appear? There is no formula in that column. Bear in mind that you cannot have both a formula and a manual entry in the same cell. I am saying this because it is not entirely clear what you are trying to achieve.
Last edited by AliGW; 03-26-2017 at 12:14 PM. Reason: OP changed thread title - thank you.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
clearly there's no formula in lines 7 & 9 because that's what's i want the formula to do - to simply stop if there's "No" extension after the expiration date so this is an example.
Look at other lines where there's a formula such as line 8 - i need that formula to be updated with the criteria above.
not sure why the title needs to improving?! i need help with IF formula since that's what I have in my sheet.
updated thread title
The IF formula can be used in many ways and your title is just too general. Then you say, "clearly there's no formula in lines 7 & 9 because that's what's i want the formula to do". Well, I'm afraid what you have given us is far from clear. Try changing your title to "Adjust IF Formula to Stop Calculation if Condition Not Met". To do this, you need to go back and edit the opening post of the thread and go advanced. If you can't work out how to do this, ask and I will do it for you.
Last edited by AliGW; 03-26-2017 at 01:11 PM.
you clearly don't care about helping forum members - i didn't sign up here for someone to give hard time about thread title - i signed up here to get help.
if you cared to look the period tab properly - you would've understood my request right away.
Paul - thank you for changing your title. It will help others to help you.
Now, I have looked at your workbook carefully and I am not quite clear about what you are trying to do. Please remember that what is plain and obvious to you, who have been working closely on this problem, is not necessarily so to others.
If you look carefully at the many posts I make on these forums, you will see that I care very much about helping people out, in fact that is my major motivation for being here. You will be pleased to know that you are no exception.
Try stating your problem again.
Could you please explain EXACTLY what you mean by this?
If a contract has an expiration date and "NO" as an extension as per columns AO and AP in the period tab.
Then I would like the formula to stop as per two highlighted yellow lines (7 & 9 in the period tab)
If a contract has a "yes", then i would like to keep the formula.
so i simply need updating the formula that's currently in the Period tab.
IF you take a look at Perid tab in the file that I had attached.
There is a IF formula from line 4 to line 29 which calculates contract pricing based on dates.
What I want this formula to do is to take into consideration columns AO and AP in the "Period" tab.
For example if we take line 7 - which is GE contract - based on expiration which is 28-apr-17 in column AO and "NO" column AP.
I want the formula to stop after the expiration dates. which is why from column G there's no formula as i had deleted it manually. I did the same in line 9 for Bank of china as an example.
Also pay attention to dates in line 2 & 3 in the period tab.
If contract has a "yes" as an extension then i want my formula to stay as I believe it's correct .
Right - I think I understand now - thanks for the clarification. Try this in B7 dragged across:
=IF(AND(B$2>$AO$7,$AP$7="no"),"",(IF(B$3<'Input Sheet'!$F4,'Input Sheet'!$E4/13,(IF(B$2>'Input Sheet'!$I4,'Input Sheet'!$K4/13,(IF((AND(B$2>'Input Sheet'!$F4,B$3<'Input Sheet'!$I4)),'Input Sheet'!$H4/13,(IF(AND(B$2<='Input Sheet'!$F4,B$3>='Input Sheet'!$F4),(((TEXT('Input Sheet'!$F4-Period!B$2,$A$1)+1)/28*'Input Sheet'!$E4/13)+(TEXT(B$3-'Input Sheet'!$F4,$A$1)/28*'Input Sheet'!$H4/13)),IF(AND(B$2<='Input Sheet'!$I4,B$3>='Input Sheet'!$I4),(((TEXT('Input Sheet'!$I4-Period!B$2,$A$1)+1)/28*'Input Sheet'!$H4/13)+(TEXT(B$3-'Input Sheet'!$I4,$A$1)/28*'Input Sheet'!$K4/13)),"probeef"))))))))))
no that doesn't work - I get "FALSE" as my outcome
Well, it works here! I've added it to the first yellow row in your file. Have a look.
yes -that's seem like it's working. just gotta update cell reference in your formula.
thank you very much
You're welcome! Glad we got there in the end.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks