and / or function

[robl]

I have an input sheet to track all monthly accruals for invoices. I want to
be able to list them on monthly sheets in the same workbook to track unpaid
invoices untill they are paid. Unpaid invoices need to re-accrue on the
following month (months) sheet. Here is the formula that has me stumped.

=IF(ISERR(SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A$1004)))),ROWS($1:1))),"",INDEX(Sheet1!A$4:A$1004,SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A$1004)))),ROWS($1:1))))

This formula is in cell A4 on sheet 2 The range on sheet 1 is A4:A1004 and
I am listing the info from col A - col H in each row returned. Col I contains
the paid dete for each invoice. I am concantinating cols E & F into col E on
sheet 2 but that function is not in this formula.

What I have learned about these functions has come from trial and error
using posts from this community.

 

[vezerid]

I have an input sheet to track all monthly accruals for invoices. I want to
be able to list them on monthly sheets in the same workbook to track unpaid
invoices untill they are paid. Unpaid invoices need to re-accrue on the
following month (months) sheet. Here is the formula that has me stumped.

=IF(ISERR(SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A$1004)))),ROWS($1:1))),"",INDEX(Sheet1!A$4:A$1004,SMALL(IF(OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004="")),ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A$1004)))),ROWS($1:1))))

This formula is in cell A4 on sheet 2 The range on sheet 1 is A4:A1004 and
I am listing the info from col A - col H in each row returned. Col I contains
the paid dete for each invoice. I am concantinating cols E & F into col E on
sheet 2 but that function is not in this formula.

What I have learned about these functions has come from trial and error
using posts from this community.

And what exactly is the problem?

 

[robl]

The or function does not return accruals no paid date. ( eg. blank date in
col I) FYI I do commit the formula with "cse"

 

[vezerid]

The key, I believe is in this:

OR((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1),(Sheet1!$A
$4:$A$1004<$B$2)*(Sheet1!$I$4:$I$1004=""))

You are including a record if its A:A value is equal to B2 (what is in
B2?) and the paid date is less than F1 OR if its A:A value is less
than B2 and the paid date is blank. I don;t know the exact logic
behind this, but I can tell you there is a problem with using OR.

Logical functions do not work well in array formulas. You are partly
handling this by using multiplication but you still have trouble with
OR. If you want to use OR-functionality in an array formula, what
would be
OR(x,y)
where x and y are logical expressions returning T/F, now should become
(x+y)>0
Hence, instead of your present OR construct:

(Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1)+(Sheet1!$A$4:$A
$1004<$B$2)*(Sheet1!$I$4:$I$1004="")>0

The entire formula now being:

=IF(ISERR(SMALL(IF((Sheet1!$A$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F
$1)+(Sheet1!$A$4:$A$1004<$B$2)*(Sheet1!$I$4:$I
$1004="")>0,ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A
$1004)))),ROWS($1:1))),"",INDEX(Sheet1!A$4:A$1004,SMALL(IF((Sheet1!$A
$4:$A$1004=$B$2)*(Sheet1!$I$4:$I$1004<$F$1)+(Sheet1!$A$4:$A$1004<$B
$2)*(Sheet1!$I$4:$I$1004="")>0,ROW(INDIRECT("1:"&ROWS(Sheet1!A$4:A
$1004)))),ROWS($1:1))))

If this still does not work maybe we need to reexamine the logic of
your formulas.

HTH
Kostis Vezerides

 

[robl]

B2 is a number eg. 0701....0702 for the GL period not a formula

 

[Beege]

B2 is a number eg. 0701....0702 for the GL period not a formula

rob1

Been my experience that "0701" should not be a number, but text, unless
you want to perform math on it...

Just my 2cents

Beege

 

[robl]

Beege
You are right it is text because of leading zero.

 

[robl]

The logic is.... if A:A value = B2 and the paid date is less than F1 (F1 =
end of current GL period) then these invoices are accrued in the current
period. OR.... if A:A value < B2 and the paid date is blank then these
invoices are re-accrued in the current period.

 

[vezerid]

OK, your logic is correctly referred to in the formula (provided both
B2 and the values in A:A are of the same data type, so that you don't
get a bug on the equality).

Thus, I believe you must consider the equivalent that I suggested
instead of OR in teh array formula.

HTH
Kostis

 

[robl]

Vezrerid

I changed the OR function as soon as I got your post and the logic part is
still not working The invoices returned are mostly those with a blank paid
date I haven't figured out which part of the logic is not working. I think it
is related to the I:I< F1 maybe I don't need the date restraint on that part.
I'll let you know.

 

[robl]

Vezerid

That finally fixed it. I deleted the date resriction because I really want
all current period accruals + old unpaid accruals. Now I have two small
questions....1. How can I have the formula show blank when there is no
result? 2. why did you put >0 the revised formula? I tried with them out
and I didn't see a difference.

 

[vezerid]

Rob,
glad it worked (as much as it did...). In answering your questinos:
1. Your original formula was indeed leaving it blank when nothing was
found. This is the philosophy:

=IF(ISERROR(something),"",INDEX(something))

The _something_ was where we intervened. Part of the _something_ is
the conditions with AND/OR. Thus the formula tests if _something_
exists; if it does not it returns "". Is it not doing this now?

2. The idea of >0 is the following: We replace AND/OR with
Multiplication/Addition. Inside an IF's first argument, where Excel
expects a logical value, if we instead supply a numeric expression, it
will be converted to True/False as follows:
0 -> False, Anything <> 0 -> True.

If a and b are logical expressions then the following two are
equivalent:
=IF(OR(a, b), xxx, yyy)
=IF(a+b, xxx, yyy)

In this context simple addition is enough b/c we don;t care if both
conditions are true and produce 2 as a result. It is <>0, hence true.
Thus, in your context the >0 was not really necessary b/c the
operations were made in a logical context and all we cared about was
that it is nonzero anyway. The >0 is necessary when we have formulas
with the IF philosophy but without IF. Example:

A2 contains points collected. B2 contains victories in a tournament.
Bonus is INT(A2/100)+B2. But to get the bonus I must either have more
than 1000 points or more than 3 victories. Without an IF:

=(INT(A2/100)+B2)*((A2>1000)+(B2>3)>0)

If I don't use the >0 then, if both conditions hold, I will get double
the bonus. The >0 basically equates all non-zero values to TRUE, which
is then converted to 1 when multiplied.

HTH
Kostis Vezerides