A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

INDIRECT reference or ?



 
 
Thread Tools Display Modes
  #1  
Old April 24th, 2010, 03:01 PM posted to microsoft.public.excel.worksheet.functions
Tom
external usenet poster
 
Posts: 1,359
Default INDIRECT reference or ?

Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10, 1.95,
1.55. I'd like to find all cells that have a value of less than 1.50 (not
less than or equal in this example). Hence, for Column AM, I'd normally use
something like =IF(AL11.50,"True","False") for each of the cells of AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a "False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION (less
than, less than or equal to, equal to, greater than, greater than or equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries
EACH time I want to do a comparison!) H3675 here is a cell that contains a
date, AL3675 is cell #3675 in column AL that contains a value (like the 1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value that
I use (the 1.50 used in the above example-I can do this!); the "LE" is less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't do
value comparisons and I'd like to make my comparisons variable by COLUMN (AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA

  #2  
Old April 24th, 2010, 04:11 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default INDIRECT reference or ?

See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50 (not
less than or equal in this example). Hence, for Column AM, I'd normally
use
something like =IF(AL11.50,"True","False") for each of the cells of AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION (less
than, less than or equal to, equal to, greater than, greater than or equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries
EACH time I want to do a comparison!) H3675 here is a cell that contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't do
value comparisons and I'd like to make my comparisons variable by COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA



  #3  
Old April 24th, 2010, 07:05 PM posted to microsoft.public.excel.worksheet.functions
Tom
external usenet poster
 
Posts: 1,359
Default INDIRECT reference or ?

Ok. great idea... now here's what I just tried but I received the #Value error:
=IF(C45"",if(Concatenate($AS$2,Row(),$AS$4,$AS$3 ),$AS$4,""),"")
where C45=04/23/2010 (a date)
$AS$2="H" (column)
Row() = Current row looking at (I guess I could change C45 to be the same)
$AS$3=0.4 (the value I'm looking to compare to that I entered for a search)
$AS$4)="=" (the operational compare symbols)
Upon using the Evaluation Formula I get to
IF(TRUE,IF("H45=0.4",$AS$4,""),"") with the next Evaluate step showing the
#VALUE! error with IF(TRUE,#VALUE!,"").
All "AS" cells [AS2, AS3 and AS4] are formatted as General including the
cell holding the above formula.
What am I doing wrong here?
TIA!!

"T. Valko" wrote:

See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50 (not
less than or equal in this example). Hence, for Column AM, I'd normally
use
something like =IF(AL11.50,"True","False") for each of the cells of AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION (less
than, less than or equal to, equal to, greater than, greater than or equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries
EACH time I want to do a comparison!) H3675 here is a cell that contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't do
value comparisons and I'd like to make my comparisons variable by COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA



.

  #4  
Old April 24th, 2010, 07:06 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default INDIRECT reference or ?

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))


We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd normally
use
something like =IF(AL11.50,"True","False") for each of the cells of AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION (less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries
EACH time I want to do a comparison!) H3675 here is a cell that contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't
do
value comparisons and I'd like to make my comparisons variable by COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





  #5  
Old April 24th, 2010, 10:05 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default INDIRECT reference or ?

=IF(C45"",if(Concatenate($AS$2,Row(),$AS$4,$AS$ 3),$AS$4,""),"")

Let's see if I understand what you're trying to "say" with that formula.

Let's assume the formula is entered on row 2 for the sake of the Row()
function.

If C45 is not blank then execute the inner IF. If C45 is blank (or empty)
return blank.

The inner IF:

If H2 = 0.4 return the string "=". If H2 is not = 0.4 return blank.

If that's what you're trying to do try it like this:

=IF(C45"",IF(COUNTIF(INDIRECT($AS$2&ROW()),""&$A S$4&$AS$3&""),$AS$4,""),"")

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Ok. great idea... now here's what I just tried but I received the #Value
error:
=IF(C45"",if(Concatenate($AS$2,Row(),$AS$4,$AS$3 ),$AS$4,""),"")
where C45=04/23/2010 (a date)
$AS$2="H" (column)
Row() = Current row looking at (I guess I could change C45 to be the same)
$AS$3=0.4 (the value I'm looking to compare to that I entered for a
search)
$AS$4)="=" (the operational compare symbols)
Upon using the Evaluation Formula I get to
IF(TRUE,IF("H45=0.4",$AS$4,""),"") with the next Evaluate step showing
the
#VALUE! error with IF(TRUE,#VALUE!,"").
All "AS" cells [AS2, AS3 and AS4] are formatted as General including the
cell holding the above formula.
What am I doing wrong here?
TIA!!

"T. Valko" wrote:

See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd
normally
use
something like =IF(AL11.50,"True","False") for each of the cells of
AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION
(less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the
above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+
entries
EACH time I want to do a comparison!) H3675 here is a cell that
contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my
value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't
do
value comparisons and I'd like to make my comparisons variable by
COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA



.



  #6  
Old April 25th, 2010, 08:53 PM posted to microsoft.public.excel.worksheet.functions
Tom
external usenet poster
 
Posts: 1,359
Default INDIRECT reference or ?

Thanks for the responses...I've updated things but I'm still having issues.
At this time I don't need to use the COUNTIF unless you have a specific
reason. I just need to know, by row, IF the value I'm looking at is , =,
=, , = than the value I'm comparing to.
When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"=","" ))
but if I use this formula for $AS$4="=" and $AS$3="0.4" it does NOT work:
IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2& Row()&$AS$4&$AS$3),$AS$4,""),"123")
I still get the #VALUE error.
The calculation steps show:
IF(TRUE,if("H45=0.4",$AS$4,"??"),"123") and then the next step shows the
#VALUE error as IF(TRUE,#Value!,"123"). Keep in mind that the cells are
General for $AS$3 and $AS$4.
What am I doing wrong here? Seems so simple but I can't pierce the issue! ;-(
TIA

"T. Valko" wrote:

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))


We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd normally
use
something like =IF(AL11.50,"True","False") for each of the cells of AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION (less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+ entries
EACH time I want to do a comparison!) H3675 here is a cell that contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND doesn't
do
value comparisons and I'd like to make my comparisons variable by COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





.

  #7  
Old April 26th, 2010, 02:51 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default INDIRECT reference or ?

At this time I don't need to use the COUNTIF
unless you have a specific reason.


Yes, the reason is very specific!

You *can't* build formulas by concatenating all the components like you're
trying to do. When you concatenate all the components all you have is a TEXT
string that looks like a formula, but it's not a formula.

COUNTIF is one of a couple of functions where you can build the criteria
expression through concatenation. And, since you seem to be comparing
numeric values, COUNTIF makes it possible to do what you want (as far as I
understand what you want to do).

To build a cell/range reference you *must* use the INDIRECT function.

When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"="," "))


Are you sure that formula works? It's missing a closing ) for the
CONCATENATE function. Also, you're not really referencing "C&ROW()" as a
cell reference. It's just a TEXT string that looks like a cell reference as
I noted above.

Let's assume that formula is entered in cell A1. So:

"C"&Concatenate((ROW())) evaluates to the TEXT string "C1". Note the
quotations marks around "C1". "C1" IS NOT being evaluated as a valid cell
reference.

To get a valid cell reference you'd have to use INDIRECT:

INDIRECT("C"&ROW())

IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2 &Row()&$AS$4&$AS$3),$AS$4,""),"123")


Nah, that isn't going to work for the reason(s) I explained above.

Try it like this:

=IF(INDIRECT("C"&ROW())"",IF(COUNTIF(INDIRECT($A S$2&Row()),""&$AS$4&$AS$3&""),$AS$4,""),123)

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Thanks for the responses...I've updated things but I'm still having
issues.
At this time I don't need to use the COUNTIF unless you have a specific
reason. I just need to know, by row, IF the value I'm looking at is ,
=,
=, , = than the value I'm comparing to.
When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"=","" ))
but if I use this formula for $AS$4="=" and $AS$3="0.4" it does NOT work:
IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2& Row()&$AS$4&$AS$3),$AS$4,""),"123")
I still get the #VALUE error.
The calculation steps show:
IF(TRUE,if("H45=0.4",$AS$4,"??"),"123") and then the next step shows the
#VALUE error as IF(TRUE,#Value!,"123"). Keep in mind that the cells are
General for $AS$3 and $AS$4.
What am I doing wrong here? Seems so simple but I can't pierce the issue!
;-(
TIA

"T. Valko" wrote:

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))


We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd
normally
use
something like =IF(AL11.50,"True","False") for each of the cells of
AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION
(less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the
above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+
entries
EACH time I want to do a comparison!) H3675 here is a cell that
contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like
the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my
value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND
doesn't
do
value comparisons and I'd like to make my comparisons variable by
COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





.



  #8  
Old April 26th, 2010, 12:36 PM posted to microsoft.public.excel.worksheet.functions
Tom
external usenet poster
 
Posts: 1,359
Default INDIRECT reference or ?

Well... CONGRATULATIONS!! It worked! (the last suggestion under your "Try it
like this"
I NEVER would have selected COUNTIF!! Never used it in the setting you
suggested nor have I seen it that way!
THANKS!
BTW, is there a website, book, lesson series, online class, etc. that you'd
suggest? I thoroughly enjoy these challenges but I'm limited on time! Also,
any debugging techniques would be appreciated.

"T. Valko" wrote:

At this time I don't need to use the COUNTIF
unless you have a specific reason.


Yes, the reason is very specific!

You *can't* build formulas by concatenating all the components like you're
trying to do. When you concatenate all the components all you have is a TEXT
string that looks like a formula, but it's not a formula.

COUNTIF is one of a couple of functions where you can build the criteria
expression through concatenation. And, since you seem to be comparing
numeric values, COUNTIF makes it possible to do what you want (as far as I
understand what you want to do).

To build a cell/range reference you *must* use the INDIRECT function.

When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"="," "))


Are you sure that formula works? It's missing a closing ) for the
CONCATENATE function. Also, you're not really referencing "C&ROW()" as a
cell reference. It's just a TEXT string that looks like a cell reference as
I noted above.

Let's assume that formula is entered in cell A1. So:

"C"&Concatenate((ROW())) evaluates to the TEXT string "C1". Note the
quotations marks around "C1". "C1" IS NOT being evaluated as a valid cell
reference.

To get a valid cell reference you'd have to use INDIRECT:

INDIRECT("C"&ROW())

IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2 &Row()&$AS$4&$AS$3),$AS$4,""),"123")


Nah, that isn't going to work for the reason(s) I explained above.

Try it like this:

=IF(INDIRECT("C"&ROW())"",IF(COUNTIF(INDIRECT($A S$2&Row()),""&$AS$4&$AS$3&""),$AS$4,""),123)

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Thanks for the responses...I've updated things but I'm still having
issues.
At this time I don't need to use the COUNTIF unless you have a specific
reason. I just need to know, by row, IF the value I'm looking at is ,
=,
=, , = than the value I'm comparing to.
When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"=","" ))
but if I use this formula for $AS$4="=" and $AS$3="0.4" it does NOT work:
IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2& Row()&$AS$4&$AS$3),$AS$4,""),"123")
I still get the #VALUE error.
The calculation steps show:
IF(TRUE,if("H45=0.4",$AS$4,"??"),"123") and then the next step shows the
#VALUE error as IF(TRUE,#Value!,"123"). Keep in mind that the cells are
General for $AS$3 and $AS$4.
What am I doing wrong here? Seems so simple but I can't pierce the issue!
;-(
TIA

"T. Valko" wrote:

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75, 1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than 1.50
(not
less than or equal in this example). Hence, for Column AM, I'd
normally
use
something like =IF(AL11.50,"True","False") for each of the cells of
AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION
(less
than, less than or equal to, equal to, greater than, greater than or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the
above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+
entries
EACH time I want to do a comparison!) H3675 here is a cell that
contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like
the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my
value
that
I use (the 1.50 used in the above example-I can do this!); the "LE" is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND
doesn't
do
value comparisons and I'd like to make my comparisons variable by
COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





.



.

  #9  
Old April 26th, 2010, 10:34 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default INDIRECT reference or ?

Ok, good deal!

As far as "expanding your Excel horizons", IMHO you'll learn more from
reading/participating in forums/newsgroups like this one. You'll see/be
exposed to every possible question/problem there is dealing with Excel. I
would say that 75% of what I know about Excel I learned right here. The
other 25% was trial and error experimenting.

Books and websites are OK but they're somewhat limited by the generalized
topics they cover. I don't think there's a single book/website that covers a
broad range of topics in *great detail*. I'm a "details" kind of guy!

The best debugging technique (IMHO) is using the formula auditing tools,
especially the evaluate formula command. I think you said you use this.

It takes a long time (years!) to learn this stuff!

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Well... CONGRATULATIONS!! It worked! (the last suggestion under your "Try
it
like this"
I NEVER would have selected COUNTIF!! Never used it in the setting you
suggested nor have I seen it that way!
THANKS!
BTW, is there a website, book, lesson series, online class, etc. that
you'd
suggest? I thoroughly enjoy these challenges but I'm limited on time!
Also,
any debugging techniques would be appreciated.

"T. Valko" wrote:

At this time I don't need to use the COUNTIF
unless you have a specific reason.


Yes, the reason is very specific!

You *can't* build formulas by concatenating all the components like
you're
trying to do. When you concatenate all the components all you have is a
TEXT
string that looks like a formula, but it's not a formula.

COUNTIF is one of a couple of functions where you can build the criteria
expression through concatenation. And, since you seem to be comparing
numeric values, COUNTIF makes it possible to do what you want (as far as
I
understand what you want to do).

To build a cell/range reference you *must* use the INDIRECT function.

When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"="," "))


Are you sure that formula works? It's missing a closing ) for the
CONCATENATE function. Also, you're not really referencing "C&ROW()" as a
cell reference. It's just a TEXT string that looks like a cell reference
as
I noted above.

Let's assume that formula is entered in cell A1. So:

"C"&Concatenate((ROW())) evaluates to the TEXT string "C1". Note the
quotations marks around "C1". "C1" IS NOT being evaluated as a valid cell
reference.

To get a valid cell reference you'd have to use INDIRECT:

INDIRECT("C"&ROW())

IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2 &Row()&$AS$4&$AS$3),$AS$4,""),"123")


Nah, that isn't going to work for the reason(s) I explained above.

Try it like this:

=IF(INDIRECT("C"&ROW())"",IF(COUNTIF(INDIRECT($A S$2&Row()),""&$AS$4&$AS$3&""),$AS$4,""),123)

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Thanks for the responses...I've updated things but I'm still having
issues.
At this time I don't need to use the COUNTIF unless you have a specific
reason. I just need to know, by row, IF the value I'm looking at is ,
=,
=, , = than the value I'm comparing to.
When I use this formula in a cell it works:
IF("C"&Concatenate((ROW())="",if(H45=0.4,"=","" ))
but if I use this formula for $AS$4="=" and $AS$3="0.4" it does NOT
work:
IF("C"&Concatenate(ROW()"",IF(Concatenate($AS$2& Row()&$AS$4&$AS$3),$AS$4,""),"123")
I still get the #VALUE error.
The calculation steps show:
IF(TRUE,if("H45=0.4",$AS$4,"??"),"123") and then the next step shows
the
#VALUE error as IF(TRUE,#Value!,"123"). Keep in mind that the cells
are
General for $AS$3 and $AS$4.
What am I doing wrong here? Seems so simple but I can't pierce the
issue!
;-(
TIA

"T. Valko" wrote:

Improvement

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

We can eliminate the CONCATENATE function:

=COUNTIF(A1,""&E$1&F$1&"")

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
See if this points you in the right direction.

You want to identify numbers in a range of cells that are 50.

E1 =
F1 = 50

A1 = 50
A2 = 99
A3 = 44
A4 = 66
A5 = 50.5

Enter this formula in B1 and copy down to B5:

=COUNTIF(A1,CONCATENATE("",E$1,F$1,""))

--
Biff
Microsoft Excel MVP


"Tom" wrote in message
...
Excel 2007 all MS updates
I need to search ANY column of numbers, say Column AL, to determine
if
any
of the values are below or above a value that I'm wanting to find.

Here's an example: in Column AL the values are 1.20, 1.50, 1.75,
1.10,
1.95,
1.55. I'd like to find all cells that have a value of less than
1.50
(not
less than or equal in this example). Hence, for Column AM, I'd
normally
use
something like =IF(AL11.50,"True","False") for each of the cells
of
AM1,
AM2, AM3, AM4, AM5, AM6 etc... I'd get a "True" for 1.20, 1.10 and
a
"False"
for 1.50, 1.75, 1.95, 1.55.

I'd like to GENERALIZE this by setting up a cell for the OPERATION
(less
than, less than or equal to, equal to, greater than, greater than
or
equal
to) and another cell for the VALUE I'm looking for (the 1.50 in the
above
example and a cell for the COLUMN I'm wanting to compare.

I'm struggling with the structure of the formula. Here's what I
currently
have:
=IF(H3675"",IF(AL3675=$A$3,"LE",""),""). [I change ALL 4000+
entries
EACH time I want to do a comparison!) H3675 here is a cell that
contains
a
date, AL3675 is cell #3675 in column AL that contains a value (like
the
1.20,
1.10 etc above); = is my "less than or equal here" that I'd like
to
change
to be a cell value in, say Cell AL1 where I put the =; $A$3 is my
value
that
I use (the 1.50 used in the above example-I can do this!); the "LE"
is
less
than or equal etc. (I'll probably need an LE, LT, EQ, GE, GT).
Any ideas on approaching would be appreciated as the Excel FIND
doesn't
do
value comparisons and I'd like to make my comparisons variable by
COLUMN
(AL
etc), OPERATION (LE, LT etc.) and VALUE (1.50 etc.).

TIA





.



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:32 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.