| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF FORMAT
I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR
SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
I'm afraid that not only am I unable to understand your problem, I don't like
people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Sorry about that.
I have a total that I need to move in the correct aging bucket. 850.00 that is over 66 days past due. I need to move all amounts that are 45 <75 days to the 61-75 day bucket. basic on today date. =IF(O245<75,N2,"not") 12/31/2008 1 to 45 46 to 75 76to 105 103 to 135 Over- 120 total days past due $51.50 66 "Luke M" wrote: I'm afraid that not only am I unable to understand your problem, I don't like people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Hi Dana,
Let's say you have different columns and the original amount is in column G and the Past due days in column G1-30 , 31-45, etc. copy the formula as per below =SUMPRODUCT(--($H:$H<=30),--($G:$G)) The above formula is if you are using excel 2007 otherwise =SUMPRODUCT(--($H1:$H1000<=30),--($G1:$G1000)) Copy this formula to the other columns and change 30 by 45, 60 according to your needs "DANA" wrote: Sorry about that. I have a total that I need to move in the correct aging bucket. 850.00 that is over 66 days past due. I need to move all amounts that are 45 <75 days to the 61-75 day bucket. basic on today date. =IF(O245<75,N2,"not") 12/31/2008 1 to 45 46 to 75 76to 105 103 to 135 Over- 120 total days past due $51.50 66 "Luke M" wrote: I'm afraid that not only am I unable to understand your problem, I don't like people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Hello. Thanks I copy it down and it would not work. my excel is 2003. I will
see if I can explain better. Colum F total past due. Colum G days 'past due Colum H 1 to 45 Colum I 46 to 75 Colum J 76 to 105 I need to take the total from Colum F to the correct colum basic on the due date. So if I have a amount that is 66 days past due I need to have it move to colum I but the IF patterns I do does not work. IFG145<75 place total in colum I. "Eduardo" wrote: Hi Dana, Let's say you have different columns and the original amount is in column G and the Past due days in column G1-30 , 31-45, etc. copy the formula as per below =SUMPRODUCT(--($H:$H<=30),--($G:$G)) The above formula is if you are using excel 2007 otherwise =SUMPRODUCT(--($H1:$H1000<=30),--($G1:$G1000)) Copy this formula to the other columns and change 30 by 45, 60 according to your needs "DANA" wrote: Sorry about that. I have a total that I need to move in the correct aging bucket. 850.00 that is over 66 days past due. I need to move all amounts that are 45 <75 days to the 61-75 day bucket. basic on today date. =IF(O245<75,N2,"not") 12/31/2008 1 to 45 46 to 75 76to 105 103 to 135 Over- 120 total days past due $51.50 66 "Luke M" wrote: I'm afraid that not only am I unable to understand your problem, I don't like people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Something like this should get the row-wise bucketing going for you
Assuming H1:J1 contains the limits: 45, 75, 105 and K1 contains a label: 105 In G2 down you have the # days, probably formula calculated In H2: =IF(AND($G20,$G2<=H$1),$F2,"") In I2: =IF(AND($G2H$1,$G2<=I$1),$F2,"") Copy I2 to J2 In K2: =IF(AND(ISNUMBER($G2),$G2J$1),$F2,"") Select H2:K2, copy down as far as required. The ISNUMBER is an additional check for col K, just in case col G contains formula-returned blanks: "" -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "DANA" wrote: Hello. Thanks I copy it down and it would not work. my excel is 2003. I will see if I can explain better. Colum F total past due. Colum G days 'past due Colum H 1 to 45 Colum I 46 to 75 Colum J 76 to 105 I need to take the total from Colum F to the correct colum basic on the due date. So if I have a amount that is 66 days past due I need to have it move to colum I but the IF patterns I do does not work. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Hi Dana,
In column H enter =+SUMPRODUCT(--(g1:g50001),--(g1:g5000<=45),--(f1:f5000)) In column I =+SUMPRODUCT(--(g1:g500045),--(g1:g5000<=75),--(f1:f5000)) In column J =+SUMPRODUCT(--(g1:g500075),--(g1:g5000<=105),--(f1:f5000)) In column K More than 105 =+SUMPRODUCT(--(g1:g5000105),--(f1:f5000)) "DANA" wrote: Hello. Thanks I copy it down and it would not work. my excel is 2003. I will see if I can explain better. Colum F total past due. Colum G days 'past due Colum H 1 to 45 Colum I 46 to 75 Colum J 76 to 105 I need to take the total from Colum F to the correct colum basic on the due date. So if I have a amount that is 66 days past due I need to have it move to colum I but the IF patterns I do does not work. IFG145<75 place total in colum I. "Eduardo" wrote: Hi Dana, Let's say you have different columns and the original amount is in column G and the Past due days in column G1-30 , 31-45, etc. copy the formula as per below =SUMPRODUCT(--($H:$H<=30),--($G:$G)) The above formula is if you are using excel 2007 otherwise =SUMPRODUCT(--($H1:$H1000<=30),--($G1:$G1000)) Copy this formula to the other columns and change 30 by 45, 60 according to your needs "DANA" wrote: Sorry about that. I have a total that I need to move in the correct aging bucket. 850.00 that is over 66 days past due. I need to move all amounts that are 45 <75 days to the 61-75 day bucket. basic on today date. =IF(O245<75,N2,"not") 12/31/2008 1 to 45 46 to 75 76to 105 103 to 135 Over- 120 total days past due $51.50 66 "Luke M" wrote: I'm afraid that not only am I unable to understand your problem, I don't like people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Please, Eduardo, stop advising people to start the formula with =+.
You know that = is enough, without the +, but you are likely to confuse the people whom you are advising. -- David Biddulph "Eduardo" wrote in message ... Hi Dana, In column H enter =+SUMPRODUCT(--(g1:g50001),--(g1:g5000<=45),--(f1:f5000)) In column I =+SUMPRODUCT(--(g1:g500045),--(g1:g5000<=75),--(f1:f5000)) In column J =+SUMPRODUCT(--(g1:g500075),--(g1:g5000<=105),--(f1:f5000)) In column K More than 105 =+SUMPRODUCT(--(g1:g5000105),--(f1:f5000)) "DANA" wrote: Hello. Thanks I copy it down and it would not work. my excel is 2003. I will see if I can explain better. Colum F total past due. Colum G days 'past due Colum H 1 to 45 Colum I 46 to 75 Colum J 76 to 105 I need to take the total from Colum F to the correct colum basic on the due date. So if I have a amount that is 66 days past due I need to have it move to colum I but the IF patterns I do does not work. IFG145<75 place total in colum I. "Eduardo" wrote: Hi Dana, Let's say you have different columns and the original amount is in column G and the Past due days in column G1-30 , 31-45, etc. copy the formula as per below =SUMPRODUCT(--($H:$H<=30),--($G:$G)) The above formula is if you are using excel 2007 otherwise =SUMPRODUCT(--($H1:$H1000<=30),--($G1:$G1000)) Copy this formula to the other columns and change 30 by 45, 60 according to your needs "DANA" wrote: Sorry about that. I have a total that I need to move in the correct aging bucket. 850.00 that is over 66 days past due. I need to move all amounts that are 45 <75 days to the 61-75 day bucket. basic on today date. =IF(O245<75,N2,"not") 12/31/2008 1 to 45 46 to 75 76to 105 103 to 135 Over- 120 total days past due $51.50 66 "Luke M" wrote: I'm afraid that not only am I unable to understand your problem, I don't like people yelling. Could you try to explain the problem clearer, and turn off CAPS lock? -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "DANA" wrote: I HAVE A AGING BUKET WHERE I NEED TO MOVE THE AMOUNT AROUND BY DUE DATE OR SPECAIL TERMS. DATE TOTAL DUE CURRENT 31-60 61- 90 91-120 OVER-120. HERE IS WHAT I HAVE. I GOT THE CURRENT DOWN BUT UNABLE TO GET FORMAT FOR THE OTHER NEED TO IF A31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET IF A231<61 N2 NOT... |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
Max
Thanks that works "Max" wrote: Something like this should get the row-wise bucketing going for you Assuming H1:J1 contains the limits: 45, 75, 105 and K1 contains a label: 105 In G2 down you have the # days, probably formula calculated In H2: =IF(AND($G20,$G2<=H$1),$F2,"") In I2: =IF(AND($G2H$1,$G2<=I$1),$F2,"") Copy I2 to J2 In K2: =IF(AND(ISNUMBER($G2),$G2J$1),$F2,"") Select H2:K2, copy down as far as required. The ISNUMBER is an additional check for col K, just in case col G contains formula-returned blanks: "" -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "DANA" wrote: Hello. Thanks I copy it down and it would not work. my excel is 2003. I will see if I can explain better. Colum F total past due. Colum G days 'past due Colum H 1 to 45 Colum I 46 to 75 Colum J 76 to 105 I need to take the total from Colum F to the correct colum basic on the due date. So if I have a amount that is 66 days past due I need to have it move to colum I but the IF patterns I do does not work. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF FORMAT
That's good to hear. Do take a moment to press the YES button (like the one
below) in the earlier response. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,000 Files:370 Subscribers:66 xdemechanik --- "DANA" wrote: Max Thanks that works |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
| Adding time in 24 hour format to produce hours in decimal format | Excel Worksheet Functions | |||
| Change Date Format to Specific Text Format When Copying | Excel Discussion (Misc queries) | |||
| Replace million-billion number format to lakhs-crores format | Excel Discussion (Misc queries) | |||
| how to format excel format to text format with separator "|" in s. | New Users to Excel | |||
Linear Mode
