IF FORMAT

[DANA]

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 A>31<61 NEED TO BE ABE TO PULL THE TOTAL DUE TO THAT BUCKET
IF A2>31<61 N2 NOT...

 

[Luke M]

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?

 

[DANA]

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(O2>45<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

 

[Eduardo]

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]

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.

IFG1>45<75 place total in colum I.

 

[Max]

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($G2>0,$G2<=H$1),$F2,"")
In I2: =IF(AND($G2>H$1,$G2<=I$1),$F2,"")
Copy I2 to J2
In K2: =IF(AND(ISNUMBER($G2),$G2>J$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

 

[Eduardo]

Hi Dana,
In column H enter

=+SUMPRODUCT(--(g1:g5000>1),--(g1:g5000<=45),--(f1:f5000))
In column I
=+SUMPRODUCT(--(g1:g5000>45),--(g1:g5000<=75),--(f1:f5000))
In column J
=+SUMPRODUCT(--(g1:g5000>75),--(g1:g5000<=105),--(f1:f5000))
In column K More than 105
=+SUMPRODUCT(--(g1:g5000>105),--(f1:f5000))

 

[David Biddulph]

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.

 

[DANA]

Max
Thanks that works

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($G2>0,$G2<=H$1),$F2,"")
In I2: =IF(AND($G2>H$1,$G2<=I$1),$F2,"")
Copy I2 to J2
In K2: =IF(AND(ISNUMBER($G2),$G2>J$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

 

[Max]

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