ExcelBanter - Using contents of a cell in a formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Using contents of a cell in a formula (https://www.excelbanter.com/excel-discussion-misc-queries/29682-using-contents-cell-formula.html)

Using contents of a cell in a formula

Hi, I'm trying to use the contents of a cell (as opposed to the actual cell)
in a Formula. For example €“ in the following Statement:

=AVERAGE(IF((PRICES!D$32848:D$34120=A44),PRICES!P$ 32848:P$34120))

Instead of using €˜PRICES!D$32848:D$34120, I want to use the contents of
another cell €“ lets say Cell A2

Cell A2 would contain the character expression €˜PRICES!D$32848:D$34120

So the statement above would read something like:

=AVERAGE(IF((? A2 ?),PRICES!P$32848:P$34120))

How do I get the statement above to recognize the contents of cell A2
instead of cell A2?

I know I could create a label range but what I am trying to do will work
much better if I can refer to the contents of a given cell.

I seem to remember being able to do this long ago in Lotus.

Thanks,
--
Mike

Hi!

Try this:

=AVERAGE(IF(INDIRECT(A2)=A44,PRICES!P$32848:P$3412 0))

I'm assuming you know that's an array formula.

Biff

"Mike" wrote in message
...
Hi, I'm trying to use the contents of a cell (as opposed to the actual
cell)
in a Formula. For example - in the following Statement:

=AVERAGE(IF((PRICES!D$32848:D$34120=A44),PRICES!P$ 32848:P$34120))

Instead of using 'PRICES!D$32848:D$34120', I want to use the contents of
another cell - lets say Cell A2'

Cell A2 would contain the character expression 'PRICES!D$32848:D$34120'

So the statement above would read something like:

=AVERAGE(IF((? A2 ?),PRICES!P$32848:P$34120))

How do I get the statement above to recognize the contents of cell A2
instead of cell A2?

I know I could create a label range but what I am trying to do will work
much better if I can refer to the contents of a given cell.

I seem to remember being able to do this long ago in Lotus.

Thanks,
--
Mike

Thanks for your response €“ I tried the following and both did not seem to
work. I am not sure if the INDIRECT Command picks up the contents of the
cell??

=AVERAGE(IF((INDIRECT(B96)=A3),PRICES!P$32849:P$34 121))

Cell B96 contains = PRICES!$A$3:$R$36001
Results = #N/A

And also tried €“

=AVERAGE(IF((INDIRECT(B97):INDIRECT(B98)=A4),PRICE S!P$32849:P$34121))

Cell B97 contains = PRICES!$A$3
Cell B98 contains = $R$36001
Results = #VALUE!

Not sure what else to try.

--
Mike

"Biff" wrote:

Hi!

Try this:

=AVERAGE(IF(INDIRECT(A2)=A44,PRICES!P$32848:P$3412 0))

I'm assuming you know that's an array formula.

Biff

"Mike" wrote in message
...
Hi, I'm trying to use the contents of a cell (as opposed to the actual
cell)
in a Formula. For example - in the following Statement:

=AVERAGE(IF((PRICES!D$32848:D$34120=A44),PRICES!P$ 32848:P$34120))

Instead of using 'PRICES!D$32848:D$34120', I want to use the contents of
another cell - lets say Cell A2'

Cell A2 would contain the character expression 'PRICES!D$32848:D$34120'

So the statement above would read something like:

=AVERAGE(IF((? A2 ?),PRICES!P$32848:P$34120))

How do I get the statement above to recognize the contents of cell A2
instead of cell A2?

I know I could create a label range but what I am trying to do will work
much better if I can refer to the contents of a given cell.

I seem to remember being able to do this long ago in Lotus.

Thanks,
--
Mike

Hi!

Your ranges have to be the same size!

PRICES!$A$3:$R$36001 is definitely not the same size as
PRICES!P$32849:P$34121

Biff

"Mike" wrote in message
...
Thanks for your response - I tried the following and both did not seem to
work. I am not sure if the INDIRECT Command picks up the contents of the
cell??

=AVERAGE(IF((INDIRECT(B96)=A3),PRICES!P$32849:P$34 121))

Cell B96 contains = PRICES!$A$3:$R$36001
Results = #N/A

And also tried -

=AVERAGE(IF((INDIRECT(B97):INDIRECT(B98)=A4),PRICE S!P$32849:P$34121))

Cell B97 contains = PRICES!$A$3
Cell B98 contains = $R$36001
Results = #VALUE!

Not sure what else to try.

--
Mike

"Biff" wrote:

Hi!

Try this:

=AVERAGE(IF(INDIRECT(A2)=A44,PRICES!P$32848:P$3412 0))

I'm assuming you know that's an array formula.

Biff

"Mike" wrote in message
...
Hi, I'm trying to use the contents of a cell (as opposed to the actual
cell)
in a Formula. For example - in the following Statement:

=AVERAGE(IF((PRICES!D$32848:D$34120=A44),PRICES!P$ 32848:P$34120))

Instead of using 'PRICES!D$32848:D$34120', I want to use the contents
of
another cell - lets say Cell A2'

Cell A2 would contain the character expression 'PRICES!D$32848:D$34120'

So the statement above would read something like:

=AVERAGE(IF((? A2 ?),PRICES!P$32848:P$34120))

How do I get the statement above to recognize the contents of cell A2
instead of cell A2?

I know I could create a label range but what I am trying to do will
work
much better if I can refer to the contents of a given cell.

I seem to remember being able to do this long ago in Lotus.

Thanks,
--
Mike

Thanks Biff - I truely appreciate it! It worked.
--
Mike

"Biff" wrote:

Hi!

Your ranges have to be the same size!

PRICES!$A$3:$R$36001 is definitely not the same size as
PRICES!P$32849:P$34121

Biff

"Mike" wrote in message
...
Thanks for your response - I tried the following and both did not seem to
work. I am not sure if the INDIRECT Command picks up the contents of the
cell??

=AVERAGE(IF((INDIRECT(B96)=A3),PRICES!P$32849:P$34 121))

Cell B96 contains = PRICES!$A$3:$R$36001
Results = #N/A

And also tried -

=AVERAGE(IF((INDIRECT(B97):INDIRECT(B98)=A4),PRICE S!P$32849:P$34121))

Cell B97 contains = PRICES!$A$3
Cell B98 contains = $R$36001
Results = #VALUE!

Not sure what else to try.

--
Mike

"Biff" wrote:

Hi!

Try this:

=AVERAGE(IF(INDIRECT(A2)=A44,PRICES!P$32848:P$3412 0))

I'm assuming you know that's an array formula.

Biff

"Mike" wrote in message
...
Hi, I'm trying to use the contents of a cell (as opposed to the actual
cell)
in a Formula. For example - in the following Statement:

=AVERAGE(IF((PRICES!D$32848:D$34120=A44),PRICES!P$ 32848:P$34120))

Instead of using 'PRICES!D$32848:D$34120', I want to use the contents
of
another cell - lets say Cell A2'

Cell A2 would contain the character expression 'PRICES!D$32848:D$34120'

So the statement above would read something like:

=AVERAGE(IF((? A2 ?),PRICES!P$32848:P$34120))

How do I get the statement above to recognize the contents of cell A2
instead of cell A2?

I know I could create a label range but what I am trying to do will
work
much better if I can refer to the contents of a given cell.

I seem to remember being able to do this long ago in Lotus.

Thanks,
--
Mike