Re: and / or function

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.
--
Thanks
Rob


"vezerid" wrote:

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

On Feb 28, 7:21 pm, robl <r...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.
--
Thanks
Rob

"vezerid" wrote:
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

On Feb 28, 5:57 pm, robl <r...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
The or function does not return accruals no paid date. ( eg. blank date in
col I) FYI I do commit the formula with "cse"
--
Thanks
Rob

"vezerid" wrote:
On Feb 28, 4:23 pm, robl <r...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I have an input *** 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) ***. 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 *** 2 The range on *** 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
*** 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.

--
Thanks
Rob

And what exactly is the problem?



.

Loading