In a debtors aging I have Name Invoice Amount Paid(if paid I put P and the amount is selected as Zero if not paid the value appears) then the next column onwards I have the aging eg. 0-30 days 30-60 days 60-90 days 90-120 days and older.

I have an "if" command to identify paid or not and an "if" command to identify aging. But when I combine the two with an "and" command it doest work for paid or not amounts

=(IF(AND(1<$N2,$N2<30+1),$E2,(0)))+AND($D2>0,(0),$E2)

I don't understand what you are trying to do. However, I can tell you a couple of things that are wrong with your formula.

Let's take the first part...

=(IF(AND(1<$N2,$N2<30+1),$E2,(0)))

First, formulas never have to start with a parenthesis, so we can shorten it to this:

=IF(AND(1<$N2,$N2<30+1),$E2,(0))

Second, there is no need for parenthesis around the

value_if_falseargument, so we can shorten it further to this:=IF(AND(1<$N2,$N2<30+1),$E2,0)

Ok, so what you are saying with that is:

If N2 is greater than 1 AND less than 31, return E2, otherwise return 0.

That part makes sense.

Now, as for the second part of your formula, there's a lot there that doesn't make sense.

+AND($D2>0,(0),$E2)

First, the + sign tells Excel to ADD the results of that part of the formula to the results of the first part (your IF function). + means SUM

Even though I know that that is not what you want to do, let's look at how that AND function works, so you'll understand why you are getting the results you are.

AND will return TRUE when all of the arguments it is testing are TRUE and FALSE if any one or more of them are FALSE. In addition, within Excel TRUE = 1 and FALSE = 0

So, when you use AND($D2>0,(0),$E2) the 0 argument in the middle is evaluated as FALSE causing the entire AND to be evaluated as FALSE or 0.

What you actually have then is this:

=IF(AND(1<$N2,$N2<30+1),$E2,0)

+0Since I don't know waht you are trying to do with that AND function at the end, I can't tell you how to rewrite the formula.

Maybe if you tried to explain it with some actual values, that might help.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you for your reply it was useful

Date Invoice Amount Paid 0-30 30-60 60-90 90-120

01.11.11 100,000 100,000(when the paid column does not have a "P" in the cell this should go to 0-30 days column cell)

01.10.11 50,000 P - - - -For Paid column when I put P the amount should not be picked for the ageing

It appears that you did not read the instructions found via the last lineof my previous post.Please click on the

blue lineat the bottom of this post, read the instructions found via that link and then repost your example data. Make sure that you include column letters and row numbers so that we can understand how the formula applies to the data.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll