LOTUS 123 TO EXCEL 2003

[becky]

I work for a company that is converting all of their Lotus 123 (Dos Version
Release 2.2) spreadsheets to Excel.

I do not understand this Lotus formula =AND(A9>=$AA$2,A9<=$AB$2) in cell
AC2.

The "A" column is full of dates.
AA2 has another date January 1, 2005, and AB2 has another date January 31,
2005.

A9 is a dashed line no date or pertinent information. When I can the
formula to A10 which is the first cell with a date the AC2 cell now says true
or false.

I want the formula to look at the whole column and determine which dates are
in between 01-01-05 and 01-31-05. Can someone help me write this formula.

--
bb

[Harlan Grove]

becky wrote...
>I work for a company that is converting all of their Lotus 123 (Dos
Version
>Release 2.2) spreadsheets to Excel.
>
>I do not understand this Lotus formula =AND(A9>=$AA$2,A9<=$AB$2) in
cell
>AC2.
....

This would be the Excel translation of a 123 formula. It's checking
whether the value of cell A9 falls between the values of cells AA12 and
AB12. It does this by checking if A9 is greater than or equal to AA2
*and* then whether A9 is less than or equal to AB2. The original 123
formula would have been

+A9>=$AA$2#AND#A9<=$AB$2

>A9 is a dashed line no date or pertinent information. When I can the
>formula to A10 which is the first cell with a date the AC2 cell now
says true
>or false.

That's what the AND function does in Excel. The 123 formula would have
returned 1 (corresponding to TRUE in Excel) or 0 (corresponding to
FALSE in Excel).

>I want the formula to look at the whole column and determine which
dates are
>in between 01-01-05 and 01-31-05. Can someone help me write this
formula.

Excel can do that, but how do you want to results of which cells in col
A are between these dates and which aren't displayed? It appears the
original 123 workbook displayed them as 1s (between) or 0s (not
between).

[becky]

Harlan

Thank you for the information. That is what I thought it was doing, but not
being a Lotus user I was not sure. I still need the formula to be a 0 or a 1
only in Excel. We are trying to get all of our documents convert but I am
running into a lot of these issues. Do you know how to write the Excel
version of this formula? Please help if you can.

Thanks
Becky

"Harlan Grove" wrote:

> becky wrote...
> >I work for a company that is converting all of their Lotus 123 (Dos
> Version
> >Release 2.2) spreadsheets to Excel.
> >
> >I do not understand this Lotus formula =AND(A9>=$AA$2,A9<=$AB$2) in
> cell
> >AC2.
> ....
>
> This would be the Excel translation of a 123 formula. It's checking
> whether the value of cell A9 falls between the values of cells AA12 and
> AB12. It does this by checking if A9 is greater than or equal to AA2
> *and* then whether A9 is less than or equal to AB2. The original 123
> formula would have been
>
> +A9>=$AA$2#AND#A9<=$AB$2
>
> >A9 is a dashed line no date or pertinent information. When I can the
> >formula to A10 which is the first cell with a date the AC2 cell now
> says true
> >or false.
>
> That's what the AND function does in Excel. The 123 formula would have
> returned 1 (corresponding to TRUE in Excel) or 0 (corresponding to
> FALSE in Excel).
>
> >I want the formula to look at the whole column and determine which
> dates are
> >in between 01-01-05 and 01-31-05. Can someone help me write this
> formula.
>
> Excel can do that, but how do you want to results of which cells in col
> A are between these dates and which aren't displayed? It appears the
> original 123 workbook displayed them as 1s (between) or 0s (not
> between).
>
>

[Harlan Grove]

"becky" <[email protected]> wrote...
> . . . Do you know how to write the Excel
>version of this formula? Please help if you can.
....
>>>I do not understand this Lotus formula =AND(A9>=$AA$2,A9<=$AB$2) in
>>>cell AC2.
....

If you need 1s and 0s, change this particular formula to

=--AND(A9>=$AA$2,A9<=$AB$2)

Unlike 123, Excel includes True and False values along with numbers, text
and errors. They can be converted to numeric 1 and 0 by applying any
do-nothing arithmetic operator like +0, *1 or double unary minus
[-(-(x))=-(-x)=x].