# is a named range the solution?

#### unstuck

##### New Member
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!

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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

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.

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).

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")

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.

Replies
4
Views
196
Replies
5
Views
210
Replies
13
Views
336
Replies
14
Views
533
Replies
3
Views
329

1,221,008
Messages
6,157,357
Members
451,416
Latest member
Ilu

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back