how to combine two if formulars

December 13, 2011 at 23:23:04
Specs: Windows 7
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

See More: how to combine two if formulars

Report •

December 14, 2011 at 10:39:26
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...


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


Second, there is no need for parenthesis around the value_if_false argument, so we can shorten it further to this:


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.


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:


Since 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.

Report •

December 15, 2011 at 00:25:37
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

Report •

December 15, 2011 at 04:12:42
It appears that you did not read the instructions found via the last line of my previous post.

Please click on the blue line at 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.

Report •

Related Solutions

Ask Question