|
Missing "Need"
Hi,
I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas? |
Missing "Need"
Ag120 is greater than r4 ithe cell should populate with ""
what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas? |
Missing "Need"
Because the date in AG120 is greater than the date in R4. Try
=IF($AG120 <=R$4, "", IF($AH120 <=R$4, "", "need")) "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas? |
Missing "Need"
On Mar 5, 2:16 pm, bj wrote:
Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-) |
Missing "Need"
The formula is doing what you've asked it to do.
23/01/2007 = 01/01/2007 $AG120 =R$4 hence you get "" from the first IF() function, and you don't get as far as the alternative path to the second IF(). -- David Biddulph "AlanStotty" wrote in message oups.com... Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas? |
Missing "Need"
No. Not any clearer.
I'm not sure which cell you are looking for being in the same month as which other cell, but try =IF(MONTH(ref1)=MONTH(ref2),"need"... If you want to to be same month and year, then =IF(AND(MONTH(ref1)=MONTH(ref2),YEAR(ref1)=YEAR(re f2)),"need"... -- David Biddulph "AlanStotty" wrote in message oups.com... I want it to populate with "need" as it is within that month. Hope that is clearer. :-) On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- |
Missing "Need"
not really September 2 is not in the same month as January 1
So I am more confused as to what you need. there are four options A AG120=r4,AH120<=r4 B AG120<r4,AH120<=r4 C AG120=r4,AH120r4 D AG120<r4,AH120r4 with your current equation, only D would give a "Need" response the others would give "". your data would be in option C whatr response do you need for each of the options? and please explain more about your "within the month" comment "AlanStotty" wrote: On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-) |
Missing "Need"
On Mar 5, 3:00 pm, bj wrote:
not really September 2 is not in the same month as January 1 So I am more confused as to what you need. there are four options A AG120=r4,AH120<=r4 B AG120<r4,AH120<=r4 C AG120=r4,AH120r4 D AG120<r4,AH120r4 with your current equation, only D would give a "Need" response the others would give "". your data would be in option C whatr response do you need for each of the options? and please explain more about your "within the month" comment "AlanStotty" wrote: On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-)- Hide quoted text - - Show quoted text - I have put an example of what I want: 01/01/2007 01/02/2007 01/03/2007 01/04/2007 01/05/2007 FromDate ToDate need need need 12/02/2207 15/04/2007 As you can see if the date is within the month, I want to put need otherwise leave blank. Hope this is clearrrrrrrrerrrrrr. :-) |
Missing "Need"
Are you sure that this isn't what you need?
=IF($AG120 =R$4, IF($AH120 <=R$4, "", "need"),"") Not certain what you are trying but to me this makes more sense. TK On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: The formula is doing what you've asked it to do. 23/01/2007 = 01/01/2007 $AG120 =R$4 hence you get "" from the first IF() function, and you don't get as far as the alternative path to the second IF(). -- David Biddulph "AlanStotty" wrote in message oups.com... Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas? |
Missing "Need"
On Mar 5, 3:59 pm, "Cortez" wrote:
Are you sure that this isn't what you need? =IF($AG120 =R$4, IF($AH120 <=R$4, "", "need"),"") Not certain what you are trying but to me this makes more sense. TK On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: The formula is doing what you've asked it to do. 23/01/2007 = 01/01/2007 $AG120 =R$4 hence you get "" from the first IF() function, and you don't get as far as the alternative path to the second IF(). -- David Biddulph "AlanStotty" wrote in message roups.com... Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - Try this link for example, it should open an excel file. Shows you what I want, just need the formula so it can work it out itself. Thanks for bearing with me. http://spreadsheets.google.com/fm?ke...3497&f mcmd=4 |
Missing "Need"
Since the format of your data got lost in the respose
I assume (shudder) that 12/02/2007 (Not 2207) is in AG120 15/04/2007 is in AH120 and that the three needs are under the 01/02, 01/03, 01/04 in row 4 that even though the 01/02 is less than the 12/02 you still want the "Need" try =if(and(R$4=date(year($AG120),month($AG120),1),R$ 4<=date(year($AH120),month($AH120)+1,0)),"need","" ) If I am wrong and you want onlt those actually between the two dates try =if(and(R$4=$AG120,R$4<=$AH120),"need","") "AlanStotty" wrote: On Mar 5, 3:00 pm, bj wrote: not really September 2 is not in the same month as January 1 So I am more confused as to what you need. there are four options A AG120=r4,AH120<=r4 B AG120<r4,AH120<=r4 C AG120=r4,AH120r4 D AG120<r4,AH120r4 with your current equation, only D would give a "Need" response the others would give "". your data would be in option C whatr response do you need for each of the options? and please explain more about your "within the month" comment "AlanStotty" wrote: On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-)- Hide quoted text - - Show quoted text - I have put an example of what I want: 01/01/2007 01/02/2007 01/03/2007 01/04/2007 01/05/2007 FromDate ToDate need need need 12/02/2207 15/04/2007 As you can see if the date is within the month, I want to put need otherwise leave blank. Hope this is clearrrrrrrrerrrrrr. :-) |
Missing "Need"
additionally I think your greater than and less than signed are reversed from
what they need to be in your original equation If you want what I think you want. in other words your original equation might work with them reversed. "bj" wrote: Since the format of your data got lost in the respose I assume (shudder) that 12/02/2007 (Not 2207) is in AG120 15/04/2007 is in AH120 and that the three needs are under the 01/02, 01/03, 01/04 in row 4 that even though the 01/02 is less than the 12/02 you still want the "Need" try =if(and(R$4=date(year($AG120),month($AG120),1),R$ 4<=date(year($AH120),month($AH120)+1,0)),"need","" ) If I am wrong and you want onlt those actually between the two dates try =if(and(R$4=$AG120,R$4<=$AH120),"need","") "AlanStotty" wrote: On Mar 5, 3:00 pm, bj wrote: not really September 2 is not in the same month as January 1 So I am more confused as to what you need. there are four options A AG120=r4,AH120<=r4 B AG120<r4,AH120<=r4 C AG120=r4,AH120r4 D AG120<r4,AH120r4 with your current equation, only D would give a "Need" response the others would give "". your data would be in option C whatr response do you need for each of the options? and please explain more about your "within the month" comment "AlanStotty" wrote: On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-)- Hide quoted text - - Show quoted text - I have put an example of what I want: 01/01/2007 01/02/2007 01/03/2007 01/04/2007 01/05/2007 FromDate ToDate need need need 12/02/2207 15/04/2007 As you can see if the date is within the month, I want to put need otherwise leave blank. Hope this is clearrrrrrrrerrrrrr. :-) |
Missing "Need"
From your Google spreadsheets link below:
"You can no longer view this document. The owner of the spreadsheet has changed the sharing options." Perhaps you can try an Excel group in your own language, as it's obviously difficult for you to explain in English what you're trying to do. -- David Biddulph "AlanStotty" wrote in message oups.com... Try this link for example, it should open an excel file. Shows you what I want, just need the formula so it can work it out itself. Thanks for bearing with me. http://spreadsheets.google.com/fm?ke...3497&f mcmd=4 On Mar 5, 3:59 pm, "Cortez" wrote: Are you sure that this isn't what you need? =IF($AG120 =R$4, IF($AH120 <=R$4, "", "need"),"") Not certain what you are trying but to me this makes more sense. TK On Mar 5, 8:32 am, "David Biddulph" <groups [at] biddulph.org.uk wrote: The formula is doing what you've asked it to do. 23/01/2007 = 01/01/2007 $AG120 =R$4 hence you get "" from the first IF() function, and you don't get as far as the alternative path to the second IF(). -- David Biddulph "AlanStotty" wrote in message roups.com... Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- |
Missing "Need"
On Mar 5, 6:12 pm, bj wrote:
Since the format of your data got lost in the respose I assume (shudder) that 12/02/2007 (Not 2207) is in AG120 15/04/2007 is in AH120 and that the three needs are under the 01/02, 01/03, 01/04 in row 4 that even though the 01/02 is less than the 12/02 you still want the "Need" try =if(and(R$4=date(year($AG120),month($AG120),1),R$ 4<=date(year($AH120),mont*h($AH120)+1,0)),"need"," ") If I am wrong and you want onlt those actually between the two dates try =if(and(R$4=$AG120,R$4<=$AH120),"need","") "AlanStotty" wrote: On Mar 5, 3:00 pm, bj wrote: not really September 2 is not in the same month as January 1 So I am more confused as to what you need. there are four options A AG120=r4,AH120<=r4 B AG120<r4,AH120<=r4 C AG120=r4,AH120r4 D AG120<r4,AH120r4 with your current equation, only D would give a "Need" response the others would give "". your data would be in option C whatr response do you need for each of the options? and please explain more about your "within the month" comment "AlanStotty" wrote: On Mar 5, 2:16 pm, bj wrote: Ag120 is greater than r4 ithe cell should populate with "" what do you want it to do? "AlanStotty" wrote: Hi, I am using the following calculation in the field: =IF($AG120 =R$4, "", IF($AH120 <=R$4, "", "need")) where AG120 = 23/01/2007, R4 = 01/01/2007 and AH120 = 02/09/2007. But it does not seem to populate with "need". Any ideas?- Hide quoted text - - Show quoted text - I want it to populate with "need" as it is within that month. Hope that is clearer. :-)- Hide quoted text - - Show quoted text - I have put an example of what I want: 01/01/2007 01/02/2007 01/03/2007 01/04/2007 01/05/2007 FromDate ToDate need need need 12/02/2207 15/04/2007 As you can see if the date is within the month, I want to put need otherwise leave blank. Hope this is clearrrrrrrrerrrrrr. :-)- Hide quoted text - - Show quoted text - That worked a treat. Very grateful. |
| All times are GMT +1. The time now is 09:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com