nested datevalue if and statements

[JoviGirl]

=if(and(B8>datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8>datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8>datevalue("10/1/05"),b8<datevalue("11/30/06"),(n8+k8)/2,(k8+n8+q8)/3))))))

if between dates 10/1/07 - 11/30/08, calc 0
if between dates 10/1/06 - 11/30/07, calc total from cell k8
if between dates 10/01/05 - 11/30/06, calc value (n8+k8)/2
otherwise calc (k8+n8+q8)/3

Formula isnt working - any help would be great...Thanks in advance

 

[Glenn]

=if(and(B8>datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8>datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8>datevalue("10/1/05"),b8<datevalue("11/30/06"),(n8+k8)/2,(k8+n8+q8)/3))))))

if between dates 10/1/07 - 11/30/08, calc 0
if between dates 10/1/06 - 11/30/07, calc total from cell k8
if between dates 10/01/05 - 11/30/06, calc value (n8+k8)/2
otherwise calc (k8+n8+q8)/3

Formula isnt working - any help would be great...Thanks in advance

Your ranges are overlapping. How would you deal with 10/30/06? Should it be k8
or (n8+k8)/2?

 

[Sean Timmons]

Not sure if it was entered exactly as below, but corrected is:

=IF(AND(B8>DATEVALUE("10/1/07"),B8<DATEVALUE("11/30/08")),0,IF(AND(B8>DATEVALUE("10/1/06"),B8<DATEVALUE("11/30/07")),K8,IF(AND(B8>DATEVALUE("10/1/05"),B8<DATEVALUE("11/30/06")),(N8+K8)/2,(K8+N8+Q8)/3)))

 

[N Harkawat]

I fixed the formula .Your brackets were not correctly closed Copy this one
belo
=IF(AND(B8>DATEVALUE("10/1/07"),B8<DATEVALUE("11/30/08")),0,IF(AND(B8>DATEVALUE("10/1/06"),B8<DATEVALUE("11/30/07")),K8,IF(AND(B8>DATEVALUE("10/1/05"),B8<DATEVALUE("11/30/06")),(N8+K8)/2,(K8+N8+Q8)/3)))

 

[Sean Timmons]

Oh, and if you want, say, 10/1/07 to be included in the first set, use >=,
not just >. Same for all the rest.

 

[JoviGirl]

Thanks so much - It works....

Not sure if it was entered exactly as below, but corrected is:

=IF(AND(B8>DATEVALUE("10/1/07"),B8<DATEVALUE("11/30/08")),0,IF(AND(B8>DATEVALUE("10/1/06"),B8<DATEVALUE("11/30/07")),K8,IF(AND(B8>DATEVALUE("10/1/05"),B8<DATEVALUE("11/30/06")),(N8+K8)/2,(K8+N8+Q8)/3)))