Please help explain this If Statement help

[itsj0nathan]

Can you someone dicifer this formula, for the life of me I cannot get it...although the final answer is 10 and Q3= 1/16/2014. If you could break it out by numbers or something that would be awesome!

=IF(-IF(Q3<DATE(2014,1,3),50,0)+IF(Q3<DATE(2014,1,6),20,0)+IF(Q3<DATE(2014,1,7),10,0)<0,15,-IF(Q3<DATE(2014,1,3),50,0)+IF(Q3<DATE(2014,1,6),20,0)+IF(Q3<DATE(2014,1,7),10,0))

Thanks
Jon

[itsj0nathan]

correction Q3 = 1/6/2014

[FDibbins]

Hi and welcome to the forum If you break that down, it becomes a bit easier to understand...
=IF(
-IF(Q3<DATE(2014,1,3),50,0)
+IF(Q3<DATE(2014,1,6),20,0)
+IF(Q3<DATE(2014,1,7),10,0)
<0,15,
-IF(Q3<DATE(2014,1,3),50,0)
+IF(Q3<DATE(2014,1,6),20,0)
+IF(Q3<DATE(2014,1,7),10,0))

Not sure if the bolded part should be negative or not?

essentially what it is doing is adding a series of values, based on how Q3 compares to specified dates (DATE(2014,1,3) etc). It then gived 15 if the sum is < 0, otherwise it adds them up

[benishiryo]

welcome to the forum, Jon. let me break up the formula into 4 parts so that you'll see ii & iv are the same.
=IF(
-IF(Q3<DATE(2014,1,3),50,0)+IF(Q3<DATE(2014,1,6),20,0)+IF(Q3<DATE(2014,1,7),10,0)
<0,15,
-IF(Q3<DATE(2014,1,3),50,0)+IF(Q3<DATE(2014,1,6),20,0)+IF(Q3<DATE(2014,1,7),10,0))

so it's saying IF part ii is less than 0, show as 15. otherwise, calculate the part ii (which is the same as iv). so the complex section is probably part ii of it. it's saying IF Q3 is:
1. less than 3 Jan 2014, show as -50 plus
2. less than 6 Jan 2014, show as 20 plus
3. less than 7 Jan 2014, show as 10

if it's 7 Jan, it will show as 0 as it doesn't qualify under any of it. 6 Jan will be lesser than 7 jan, so show as 10. 5 jan is lesser than both 2 & 3, so show 10 + 20. 2 Jan is less than all 3 of them, so it adds them up & becomes -20. since you have part iii saying if it's less than 0, show 15, it returns 15.
what i cannot understand is the need for for less than 3 Jan 2014 since it's a definite negative. so why not just:
=IF(Q3<DATE(2014,1,3),15,IF(Q3<DATE(2014,1,6),20,0)+IF(Q3<DATE(2014,1,7),10,0))

Edit: ahh Ford beat me to it. but i wrote so much i decided to just post it. =)

[FDibbins]

Edit: ahh Ford beat me to it. but i wrote so much i decided to just post it. =)

@ Ben, I know that feeling really well - spend a while creating a formula, post it and find someone beat you to it lol

[benishiryo]

@Ford:
more of typing the explanation really.
i usually refresh the page if it's just a formula. and when it's the same, i definitely won't post it. i didn't bother to even refresh for this one, since i already typed so much!

[itsj0nathan]

Your help is greatly appreciated.


ok ive replaced the formula with Bens...so it matches all of the previous except these..
same criteria but S595=1/6/1014 and the old formula showed 50, yet yours shows 25.


=IF(-IF(S595<DATE(2014,1,3),100,0)+IF(S595<DATE(2014,1,6),25,0)+IF(S595<DATE(2014,1,7),50,0)<0,15,-IF(S595<DATE(2014,1,3),100,0)+IF(S595<DATE(2014,1,6),25,0)+IF(S595<DATE(2014,1,7),50,0))