Formula that includes dates difference returns FALSE

[reemt]

Hey,
I have the following formula that is returning false whenver the dates differnce that is included in the formula is positve:

=IF(AE$2="Owner",IF(SUM(AE$1-$W7)<=0,0,IF(AND(SUM(AE$1-$W7)>0,SUM(AE$1-$W7)<=365),SUM($V7/365*(AE$1-$W7),IF(SUM(AE$1-$W7)>365,$V7)))),IF(SUM(AE$1-$W7)<=0,$V7,IF(AND(SUM(AE$1-$W7)>0,SUM(AE$1-$W7)<365),SUM($V7/365*SUM(365-SUM(AE$1-$W7)),IF(SUM(AE$1-$W7)>=365,0))))))

where AE1 is date 1 and W7 is date 2. whenever date 1 is greater than date 2, the formula is returning as FALSE.

does anyone have an idea why??

thanks

 

[jasonb75]

Try explaining what your formula is meant to do, you have your logic in the wrong order so some things are getting missed out.

The way your formula appears, you have 6 possible scenario

EA$2 = Owner and AE$1-W$7 < 0
EA$2 = Owner and AE$1-W$7 0 to 365
EA$2 = Owner and AE$1-W$7 > 365
EA$2 Not Owner and AE$1-W$7 < 0
EA$2 Not Owner and AE$1-W$7 0 to 365
EA$2 Not Owner and AE$1-W$7 > 365

What is the correct calculation for each of these scenario?

 

[reemt]

Actually the logic that I want is the following:
EA$2 = Owner and AE$1-W$7 < 0 so the return is zero
EA$2 = Owner and AE$1-W$7 0 to 365 so return is the prorata of the number of days between the 2 dates times a certain cost per day that is V7
EA$2 = Owner and AE$1-W$7 > 365 so the return is the total cost V7
EA$2 Not Owner and AE$1-W$7 < 0 so the return is V7
EA$2 Not Owner and AE$1-W$7 0 to 365so the return is the remaining number of days times V7
EA$2 Not Owner and AE$1-W$7 > 365 so the retyrn is zero cuz the full cost was acounted for wn the option is "owner"

I hope this is clear ,,

Thanks Jason ,,

 

[jasonb75]

Think this is right

=IF(AE$2="Owner",MEDIAN(0,($V7/365*(AE$1-$W7)),$V7),ABS(MEDIAN(0,-($V7/365*(365-(AE$1-W$7))),-$V7)))

 

[reemt]

Yeah it worked though i donno how it worked cuz i'm not familiar with median use and how it can be applied to my example..
Thanks Jason..

 

[jasonb75]

Looking at the median for "Owner", MEDIAN(0,($V7/365*(AE$1-$W7)),$V7), the key is the middle section.

If AE1 -W7 is less than 0 then V7/365 will multiplied by a negative value so the result will also be less than 0, making it the lowest value, V7 will be the highest, making 0 the middle (median) value.

Similarly, AE1 - W7 is greater than 365, i.e. 400, then (V7/365)*400 makes the result greater than V7, so this will be the highest value, 0 will be the lowest and V7 will be the median.

If AE1 - W7 is between 0 and 365 (inclusive) then the resulting value of (V7/365)*difference will fall between the lowest(0) and highest (V7) values, making it the median.

The "Non owner" side of the formula uses the same principal, using negative values to reverse the scale. This will probably be easier to understand if you use formula evaluation with a set of "non owner" dates that fall between 0 and 365 days difference.

I would suggest following the examples above with positive values first and making sure you understand how they work before trying with negative values.