IF function
This is the formula to calculate if assessments have happened, going to
happened or hasn't happened =IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) Every time I change the figure to test the function the response is "ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out. Where am I going wrong? |
IF function
You always get that, because the first part of your formula always gives true
in other words = x = 0 < 365 returns true for all values of x if you don't believe me, just go and type = 1 = 0 < 365 in a cell then type = NOW()-AF2=0<365 and you will see that it is always true And of course 0 < 365. Always. -- Allllen "Pasty" wrote: This is the formula to calculate if assessments have happened, going to happened or hasn't happened =IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) Every time I change the figure to test the function the response is "ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out. Where am I going wrong? |
IF function
Yeah I noticed that shortly after I wrote this - I'm trying to get the first
bit to say if between 0 and 365 (can be equal to both of these values as well) but I'm having a bit of difficulty. "Allllen" wrote: You always get that, because the first part of your formula always gives true in other words = x = 0 < 365 returns true for all values of x if you don't believe me, just go and type = 1 = 0 < 365 in a cell then type = NOW()-AF2=0<365 and you will see that it is always true And of course 0 < 365. Always. -- Allllen "Pasty" wrote: This is the formula to calculate if assessments have happened, going to happened or hasn't happened =IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) Every time I change the figure to test the function the response is "ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out. Where am I going wrong? |
IF function
Try something like this:
With A2: (a date) B2: =LOOKUP(TODAY()-A2,{-100000,0,366},{"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT","ASSESSMENT CARRIED OUT THIS YEAR","ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN"}) or Build this table on another worksheet or in a blank range on the current sheet. (I'll assume it's on the current sheet in cells Y1:Z3 -100000 ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT 0 ASSESSMENT CARRIED OUT THIS YEAR 366 ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN Then B2: =LOOKUP(TODAY()-A2,$Y$1:$Y$3,$Z$1:$Z$3) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Pasty" wrote: This is the formula to calculate if assessments have happened, going to happened or hasn't happened =IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) Every time I change the figure to test the function the response is "ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out. Where am I going wrong? |
IF function
try
=IF(and(NOW()-AF2=0,NOW()-AF2<=0365),"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) "Pasty" wrote: Yeah I noticed that shortly after I wrote this - I'm trying to get the first bit to say if between 0 and 365 (can be equal to both of these values as well) but I'm having a bit of difficulty. "Allllen" wrote: You always get that, because the first part of your formula always gives true in other words = x = 0 < 365 returns true for all values of x if you don't believe me, just go and type = 1 = 0 < 365 in a cell then type = NOW()-AF2=0<365 and you will see that it is always true And of course 0 < 365. Always. -- Allllen "Pasty" wrote: This is the formula to calculate if assessments have happened, going to happened or hasn't happened =IF(NOW()-AF2=0<365,"ASSESSMENT CARRIED OUT THIS YEAR",IF(NOW()-AF2<0,"ASSESSMENT PLANNED FOR THIS YEAR BUT NOT CARRIED OUT",IF(NOW()-AF2365,"ASSESSMENT NOT PLANNED OR FAILED TO HAPPEN",))) Every time I change the figure to test the function the response is "ASSESSMENT CARRIED OUT THIS YEAR" regardless of the figure it's working out. Where am I going wrong? |
All times are GMT +1. The time now is 04:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com