adding name values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Excel 2003

Menu: Insert => Name => Define:

N = 20
O = 30
M = 50

Let’s say cell(s):

K3 has N displayed
K4 has O displayed
K5 has M displayed

=SUM(K3:K5) displays 0

How do I make it display the number 100? As in 20+30+50=100
 
In K3: =N

Use the = sign to get the value of the defined name.

--
Kind regards,

Niek Otten

| Excel 2003
|
| Menu: Insert => Name => Define:
|
| N = 20
| O = 30
| M = 50
|
| Let's say cell(s):
|
| K3 has N displayed
| K4 has O displayed
| K5 has M displayed
|
| =SUM(K3:K5) displays 0
|
| How do I make it display the number 100? As in 20+30+50=100
|
|
 
If that's how you've defined the names you would use:

=N+O+M = 100

=INDIRECT(K3) would show 20 if K3 had N in it

=INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5) = 100

Regards

Trevor
 
You have actual values "N", "O", and "M" in those cells. You don't have the
names. You would have to use the INDIRECT function to get the sum.

=SUM(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))
 
Trevor (and Niek and William),

Thanks for the come back.

I do have the letter N in cell K3, letter O in cell K4, and letter M in cell
K5.
These letters will change with time, so that is why I assigned number values
to them. The running total for each row and column will have a consistant
value.

So, I need to display N,O,M in the cells but need a number at the end
representing a total.

I tried your suggestions:

=INDIRECT(K3) gives a #REF! error
same for
=INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5)
and for
=sum(INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5))
and
=sum(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))


Any other suggestions?
 
Why use Defined names then? Just use a lookup table and the VLOOKUP() function to connect a letter to a value.

--
Kind regards,

Niek Otten

| Trevor (and Niek and William),
|
| Thanks for the come back.
|
| I do have the letter N in cell K3, letter O in cell K4, and letter M in cell
| K5.
| These letters will change with time, so that is why I assigned number values
| to them. The running total for each row and column will have a consistant
| value.
|
| So, I need to display N,O,M in the cells but need a number at the end
| representing a total.
|
| I tried your suggestions:
|
| =INDIRECT(K3) gives a #REF! error
| same for
| =INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5)
| and for
| =sum(INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5))
| and
| =sum(INDIRECT(K3),INDIRECT(K4),INDIRECT(K5))
|
|
| Any other suggestions?
|
| "Trevor Shuttleworth" wrote:
|
| > If that's how you've defined the names you would use:
| >
| > =N+O+M = 100
| >
| > =INDIRECT(K3) would show 20 if K3 had N in it
| >
| > =INDIRECT(K3)+INDIRECT(K4)+INDIRECT(K5) = 100
| >
| > Regards
| >
| > Trevor
| >
| >
| > | > > Excel 2003
| > >
| > > Menu: Insert => Name => Define:
| > >
| > > N = 20
| > > O = 30
| > > M = 50
| > >
| > > Let's say cell(s):
| > >
| > > K3 has N displayed
| > > K4 has O displayed
| > > K5 has M displayed
| > >
| > > =SUM(K3:K5) displays 0
| > >
| > > How do I make it display the number 100? As in 20+30+50=100
| > >
| > >
| >
| >
| >
 
The spreadsheet printout will have a collection of N's, O's, and M's as well
as other letters on it. Combinations will vary, such as NNOMMM or MOOONNO
etc.

I will have a tally line across the bottom and one on the side. Horizontal
rows will equal a given number. Vertical rows will equal a different but
constant number. These numbers will indicate at a glance whether the right
combination of letters have been use.

Therefore, the values for each letter must be exact. I looked at the lookup
command as you suggested. Not sure that command handles exactly what I'm
trying to do.

In simple terms, I just want to add the values in each row and then
separately add the values in each column. However, instead of numbers being
displayed in the spreadsheet, letters are displayed.
 
Disregard what I just wrote, below.

Niek you are absolutely correct.

After going round and round with numerous functions, I got the lookup to work.

Made a Lookup table with Letters in the first column and numbers in the
second column. The syntax looks like:

=SUM(VLOOKUP(H25,B40:C46,2,FALSE)+VLOOKUP(I25,B40:C46,2,FALSE)+VLOOKUP(J25,B40:C46,2,FALSE))

The spreadsheet has 10 columns so the =sum will be a lot longer but you get
the idea.

Thanks to Trevor, William, and especially Niek; your input was very helpful,
got me running down the right road!! Thanks again!
 
Back
Top