nested datevalue if and statements
 

=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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

nested datevalue if and statements
 

JoviGirl wrote:
=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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?

nested datevalue if and statements
 

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

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

"JoviGirl" wrote:

=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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

nested datevalue if and statements
 

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

"JoviGirl" wrote:

=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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

nested datevalue if and statements
 

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

"Sean Timmons" wrote:

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

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

"JoviGirl" wrote:

=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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

nested datevalue if and statements
 

Thanks so much - It works....

"Sean Timmons" wrote:

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

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

"JoviGirl" wrote:

=if(and(B8datevalue("10/1/07"),B8<datevalue("11/30/08"),0,if(and(B8datevalue("10/1/06"),B8<datvalue("11/30/07"),k8,if(and(b8datevalue("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