MJB@cup.portal.com (Martin J Brown-Jr) (02/04/90)
I'm having a few problems with using "names" in Excel v2.1.
Consider the following mini spreadsheet:
A B C D
------------------------------
1 | | Jan | Feb | Mar |
------------------------------
2 | Rent | 700 | 700 | 700 |
------------------------------
3 | Food | 60 | 55 | 75 |
------------------------------
4 | Util | 28 | 33 | 45 |
------------------------------
5 | Total| 768 | 788 | 820 |
------------------------------
I "create names" with "top" and "left", and "apply" with the default settings
This allows me to ref a cell by name, i.e., "Goto" "Jan Util" will put me at
So far so good.
The formula for B5 could read "=SUM(B2:B4)", which gives correct results.
BUT the formula for B5 that reads "=SUM(Jan Rent:Jan Util)" does NOT give
the correct result, it returns the value of the cell to the right of the ":".
If the formula for B5 reads "=SUM(Jan Rent+Jan Food+Jan Util)" the correct
result will be given. In this case, the problem is what is the correct
format for using col and row names in range formulas.
Also, if there are no row names applied (no "rent" "food" etc.), I'm unable
to figure out how to say "Jan 4" to mean B4.
Any solutions to these problems would be most helpful.
Thanx!
- MJB -
USNET: mjb@cup.portal.com
BIX: mbrownindra@pepsi.amd.com (Indra Singhal) (02/09/90)
In article <26588@cup.portal.com> MJB@cup.portal.com (Martin J Brown-Jr) writes: > >The formula for B5 could read "=SUM(B2:B4)", which gives correct results. >BUT the formula for B5 that reads "=SUM(Jan Rent:Jan Util)" does NOT give >the correct result, it returns the value of the cell to the right of the ":". I would try not using spaces in the names.. use Jan.Rent or Jan_Rent and see if the problem persists. iNDRA | indra@amdcad.AMD.COM (Indra Singhal) (408) 749-5445 | {ames decwrl apple pyramid sun uunet}!amdcad!indra | MS 167; Box 3453; Sunnyvale, CA 94088
pwilliam@axion.bt.co.uk (Philip Williams) (02/12/90)
In article <29100@amdcad.AMD.COM>, indra@pepsi.amd.com (Indra Singhal) writes: > In article <26588@cup.portal.com> MJB@cup.portal.com (Martin J Brown-Jr) writes: > > > >The formula for B5 could read "=SUM(B2:B4)", which gives correct results. > >BUT the formula for B5 that reads "=SUM(Jan Rent:Jan Util)" does NOT give > >the correct result, it returns the value of the cell to the right of the ":". > > I would try not using spaces in the names.. use Jan.Rent or Jan_Rent and > see if the problem persists. > > > I didn't see the original posting but I've just tried it out on a MAC running Excel 2.2 and A 386 PC running Excel 2.0 (I think my Excel at home, on a 386 PC, is 2.1 and I will try that tonight) and thet do not allow a name with a space in it. If underscores are used it functions correctly. I will get back if I find anything different on Excel 2.1 Philip ********************************************************************** P M Williams pwilliams@axion.bt.co.uk RT3133 British Telecom Research Labs Martlesham Heath 'Phone 0473-642770 IPSWICH Suffolk UK Message Pager 4226754 (bureau 0345 333111) "A foolish consistency is the hobgoblin of little minds ..... Ralph Waldo Emmerson **********************************************************************