| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested if statements | Excel Worksheet Functions | |||
| Nested IF statements | New Users to Excel | |||
| Nested IF statements | Excel Discussion (Misc queries) | |||
| Help with Nested If Statements | Excel Discussion (Misc queries) | |||
| Do I need nested IF statements? | Excel Worksheet Functions | |||
Linear Mode
