• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Statement Having Issues

W

WarpathMechanic

New Member
Long time reader, HUGE fan of the site, First time poster:


Here is my dilemma. I have a date input cell (B2), and a Face Value for a product (B8). I need to calculate three different date ranges


Less than 45 days

45 days to 11 months

11 or more months


It also makes a difference whether or not this product is included in an overall package


B8 >0

B8<=0


So a table of my conditions would look like this


<45 days + B8>0 NTK

<45 term credit

45-11 + B8>0 NTK + 200 + adv eff date

45-11 term credit + 200

11+ + B8>0 NTK +adv eff date

11+ term credit


After some HEAVY nesting I come up with this IF statement:


=IF(AND(B2<TODAY()-45,B8>0),"NTK",IF(AND(B2<TODAY()-45,B8<=0),"Term Credit",IF(AND(B2>EDATE(TODAY(),-11),B2>TODAY()-45,B8>0),"NTK plus $200 plus adv eff date",IF(AND(B2>EDATE(TODAY(),-11),B2>TODAY()-45,B8<=0),"Term Credit plus $200",IF(AND(B2>EDATE(TODAY(),11),B8>0),"NTK and adv eff Date")))),"Term Credit")


But it constantly tells me I have too many arguments. Do I? Is there a way to simplify?
 
Yep, you've run into the 7 nested function limit.

We can simplify by first checking B8 to create 2 "paths", and then further simplify by using a LOOKUP function.

=IF(B8>0,LOOKUP(B2,{0,46,330},{"NTK","NTK plus $200 plus adv eff date","NTK and adv eff Date"}),LOOKUP(B2,{0,46,330},{"Term Credit","Term Credit plus $200","Term Credit"}))


Of note, it might be easier to manage/maintain if the LOOKUP table was built in some cells, rather than in the formula. Either way, the key is to first check B8 so you don't have as much nesting.
 
Luke M:


I thought as much.


I placed your formula into my worksheet and it changes based on the value of B8 (Product Amount) but it does not seem to be changing when I modify the date field (B2). Any ideas why?


I thought of building a table but ran into logistics issues with the Product amount. I could not think of how to put "any positive #" into a cell (data validation aside).


If you have any advice on the table structure, I can always hide it somewhere on the sheet.


Grand appreciation for all your help.
 
Oops. I didn't see that your formula was comparing dates. I was simply look at the number 45, and 330 (30 days * 11 months). Apologies.


For the table layout, think of a 3 x 4 table. Col 1 is the day increments, col 2 is choices if B8>0, col 3 is choices if B8<=0. For the dates, use

=TODAY()

=TODAY()+45

=EDATE(TODAY(),11)

(I think that's right...wasn't sure whether you dates were in future or past)


Then, your formula could be:

=IF(B8>0,LOOKUP(B2,DateChoices,Col1_Choices),LOOKUP(B2,DateChoices,Col2_Choices))
 
Luke, you are truly an asset.


I set up the table as you said and it works wonderfully...but only for the specified dates. This field (B2) is going to be a data entry field for any date in the past. So these dates in the table need to show ranges such as:


TODAY() and TODAY()-45

TODAY()-46 and EDATE(TODAY(),-11)

Anything beyond EDATE(TODAY(),-11)


To allow for the "rules" (columns 2&3) to be applied based on where the entered date falls in the ranges.


I think there is an article on the site regarding this issue but if you know off the top of your head then by all means let me know. I just do not know how to specify between this and this in a LOOKUP statement.


Again, grand appreciation for all your help.


*EDIT*


I now have


Start Date End Date If B8>0 If B8=0

1/7/1900 10/21/2010 NTK+adv eff date Term Credit

10/22/2010 8/6/2011 NTK+200+adv eff date Term Credit + 200

8/7/2011 9/21/2011 NTK Term Credit


and this statement found on the Range Lookup in excel article:


=IF(SUMPRODUCT(--(A24:A26<=B2)*(B24:B26>=B2))to get the date range it falls in. Now I am simply trying to get the Product amount factored in.


If I were better at (new to me) SUMPRODUCT I could break this down to get what I am looking for but I need some guidance...
 
Here's something close.


=CHOOSE(SUMPRODUCT(1+(B2>(TODAY()-45))*(B8>0)*1+(B2>(TODAY()-45))*(B8<=0)*2+(B2>(TODAY()-330))*(B2<(TODAY()-45))*(B8>0)*3+(B2>(TODAY()-330))*(B2<(TODAY()-45))*(B8<=0)*4+(B2<(TODAY()-330))*(B8>0)*5),"Term Credit","NTK","Term Credit","NTK plus $200 plus adv eff date","Term Credit plus $200","NTK and adv eff date")


By multiplying each subpart a different number, this will yield a value from 1 to 6, depending on which options are tripped. Choose then selects the appropriate text. Note that there are some different comparisons than were in your original, but I think this is what you wanted.


Be careful copying this equation around on the spreadsheet. If you only want to look at b2 and b8, fix the cell references.
 
LOOKUP is nice in that you only need to set the "next level" marks. So, let's say the last entry was 10. If I do a lookup for 100, the function will still pull from the 10 row, because that's the last record "of equal or smaller value".

With that said, looks like we need to reverse the order of the lookup table so that it works for things in the past.


Starting with smallest value possible:

=0 <---Think of this as "anything older than 11 months ago"

=EDATE(TODAY(),-11) <---This is for anything equal to or less than 11 months ago

=TODAY()-45 <---Similarly, this will trigger anything less than or equal to 45 days ago

=TODAY() <--Not sure if this line is needed. Only if there's some marker for dates in future


Does that help?
 
Mike86:


Sweet cupping cakes it works...I have no idea how even with your explanation but it works. I just got into the office so I will play around with this for a bit and see if I can't break it down and learn a trick or two.


Luke M and Mike86, Thanks for all your help. Its really appreciated and I will try and put my 2c worth in here as I can. This site is FANTASTIC.
 
A brief explanation:


Array functions allow you to make comparisons. For example, (b8>0), will give you a TRUE or FALSE. To convert this to a number, you can either put a "--" in front of the expression, effectively multiplying by -1 twice, or you can multiply it by 1. Either technique will give you a 0 or 1 for FALSE or TRUE, respectively.


In this case, you needed six different end results. All I did was make the five required comparisons individually, made sure they provided unique TRUE/FALSE responses, multiplied each by 1 through 5 to provide different numeric values, then combined them into the SumProduct statement using addition. Your default case I took care of by including a "1+" as the first part of the SumProduct. After that, I just lined up the text in the appropriate order.


It was an interesting way of using SumProduct.
 
You must log in or register to reply here.
Back
Top