| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
adding name values
Excel 2003
Menu: Insert = Name = Define: N = 20 O = 30 M = 50 Lets 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
In K3: =N
Use the = sign to get the value of the defined name. -- Kind regards, Niek Otten "AKPhil" wrote in message ... | 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 | | |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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 "AKPhil" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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 "AKPhil" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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 "AKPhil" wrote in message ... | 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 | | | "AKPhil" wrote in message | ... | 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 | | | | | |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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. "Niek Otten" wrote: 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 "AKPhil" wrote in message ... | 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 | | | "AKPhil" wrote in message | ... | 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 | | | | | |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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! "AKPhil" wrote: 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. "Niek Otten" wrote: 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 "AKPhil" wrote in message ... | 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 | | | "AKPhil" wrote in message | ... | 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 | | | | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
adding name values
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)) "AKPhil" wrote: Excel 2003 Menu: Insert = Name = Define: N = 20 O = 30 M = 50 Lets 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding Multiple low values | Excel Worksheet Functions | |||
| How can I break values apart that are in the same cell? | Excel Worksheet Functions | |||
| Adding large minute and second values | Excel Worksheet Functions | |||
| Adding Values From Different Tabs | Excel Discussion (Misc queries) | |||
| Adding values from one worksheet to another | Excel Worksheet Functions | |||
Hybrid Mode
