is a named range the solution?

[unstuck]

Hi,
How do I create a formula to copy a value from a certain cell into a formula based on information from another cell(s).
For example if
A1=John A2=smith A3=value of interest1
B1=John B2=smith B3=value of interest2
A1=John A2=Jones A3=value of interest1
B1=John B2=Jones B3=value of interest2

I would like the formula to take the value from cell in column 3, depending on which john I am interested in, and say make an average.
Of course the problem is that although the related cells will always be adjacent to each other, the spreadsheet is varaible from time to time (for example John Smith may not be there at all, or represented only once, in which case the formula calculation is unncessary)
could the formula be something like
If A1 and A2 = B1 and B2 then average A3 and B3. But instead of using specific cell addresses, use a named range?
I hope this is even remotely clear!

 

[JohnG]

Humm, dont know if this will help.

IF(COUNTIF(smith,"smith")>1,(SUM(smith_Int)/COUNTIF(smith,"smith")),0)

smith= name range
smith_int=interest range

 

[unstuck]

that sort of works, except that the values I mentioned (eg smith) were arbitrary. I will not actually know what text string will be input there. The important thing is to compare A2 to A5, if the entery is the same, carry out some math on associated cells (in B column) otherwise, skip it!
thanks, I'm further ahead than I was before.

 

[Aladin Akyurek]

On 2002-09-20 10:48, unstuck wrote:
Hi,
How do I create a formula to copy a value from a certain cell into a formula based on information from another cell(s).
For example if
A1=John A2=smith A3=value of interest1
B1=John B2=smith B3=value of interest2
A1=John A2=Jones A3=value of interest1
B1=John B2=Jones B3=value of interest2

I would like the formula to take the value from cell in column 3, depending on which john I am interested in, and say make an average.
Of course the problem is that although the related cells will always be adjacent to each other, the spreadsheet is varaible from time to time (for example John Smith may not be there at all, or represented only once, in which case the formula calculation is unncessary)
could the formula be something like
If A1 and A2 = B1 and B2 then average A3 and B3. But instead of using specific cell addresses, use a named range?
I hope this is even remotely clear!

=SUMPRODUCT((A1:A3=E1)*(B1:B3=F1),C1:C3)/MAX(1,SUMPRODUCT((A1:A3=E1)*(B1:B3=F1)))

where E1 is a value like "john" and F1 a value like "smith" (all without double quotes).

 

[paliman]

I think this will do:

In A6 type "John"
In B6 type "Smith"

In C6 array-enter this formula:

=AVERAGE(IF(CONCATENATE($A$1:$D$1,$A$2:$D$2)=CONCATENATE(A6,B6),$A$3:$D$3))

Now, change the names in A6 and/or in B6 and it will show you the averages

(to array-enter a formula you need to press "ctrl+shift+enter" instead of "enter")

 

[paliman]

Sorry, I made a mess with the references. In my prior post, I had constructed a spreadsheet as follows:

A1: John
A2: Smith
A3: Value#1

B1: John
B2: Smith
B3: Value#2

C1: John
C2: Jones
C3: Value#3

D1: John
D2: Jones
D3: Value#4

Then I wrote the formula and it worked OK.

Pali.