Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help creating formula based on cell value | Excel Discussion (Misc queries) | |||
Percent and Rank formula in one cell | Excel Discussion (Misc queries) | |||
How do I add a date formula to a cell but hide the contents with . | Excel Discussion (Misc queries) | |||
How can I write an if-then formula for 0 or less than 0 in cell t. | Excel Worksheet Functions | |||
How do I do math on a cell name in formula? | Excel Worksheet Functions |