Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Mike
 
Posts: n/a
Default 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
  #2   Report Post  
Biff
 
Posts: n/a
Default

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



  #3   Report Post  
Mike
 
Posts: n/a
Default

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




  #4   Report Post  
Biff
 
Posts: n/a
Default

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






  #5   Report Post  
Mike
 
Posts: n/a
Default

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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
need help creating formula based on cell value Brad Excel Discussion (Misc queries) 3 April 1st 05 07:51 PM
Percent and Rank formula in one cell T.R. Excel Discussion (Misc queries) 1 March 10th 05 05:05 PM
How do I add a date formula to a cell but hide the contents with . Emzy Wemzy Excel Discussion (Misc queries) 2 December 12th 04 02:48 PM
How can I write an if-then formula for 0 or less than 0 in cell t. Baz1 Excel Worksheet Functions 1 November 30th 04 05:33 PM
How do I do math on a cell name in formula? Mark Mulik Excel Worksheet Functions 3 November 23rd 04 04:43 PM


All times are GMT +1. The time now is 08:15 PM.

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

About Us

"It's about Microsoft Excel"