One of the first great improvements in VisiCalc -- surely there are some of you who remember VisiCalc -- was the ability to name a range of cells. It came close to making VisiCalc a handy digital version of linear algebra. Is that capability present in Numbers?
MacBook Pro with Retina display, OS X El Capitan (10.11.3)
In Numbers you typically break up your work into relatively small, special-purpose tables. Each column in each table acts like a named range. So does each row. In other words you can reference each row or each column by its "name", which is the value in the Header Row at the top or the Header Column on the left. Give it a try!
SG
P.S. On the Mac this becomes more evident if under Numbers > Preferences you check ' User header names as labels'. In iOS the names (labels show by default). Even with that preference turned off, however, you can input the name in formulas.
A Table is a named range
Make that table the size of the range and give it a name
SGIII and Wayne,
Thanks for your suggestions; I should have given a better description of my problem. I have a very large spreadsheet with hourly data and calculations for one and maybe two years, up to 17,000 rows. I would like to be able to identify subsets of that data by, say, months or seasons, without having to create a new table for each subset. I hesitate to create new tables because any changes in the calculations that modify the whole set would have to be reentered in each subset table. Ugh!
Or are there better ways to do it? I'd appreciate you thoughts.
An example of the data and a list of the conditions that define a subset of the data will certainly help us make suggestions. What do you want to do with the subset?
Thanks for the very quick reply. The data series are hourly solar radiation and hourly local electricity consumption. Each year is 8760 rows; the data is available for several years.This is an isolated, not a grid connected, system. The broad problem is to find the combination of solar panels, batteries and supplemental diesel generation that minimizes the cost of electricity. The calculations are somewhat complicated but are working. Unfortunately the risk and cost of failure of the entire system or just parts of it changes dramatically with the season and, even though we can't predict the likelihood of failure, the fact that we know it might happen affects what we think would be a conservative design. We'd like to be able to easily view the calculations for a season or a few months to get an idea of how the costs of failure -- basically the costs of running a diesel, storing diesel fuel and replacement parts -- change. We can recreate the spreadsheet for selected periods, but thought it might be easier to simply name ranges of dates for which we'd like to do the calculations. We might also be able to create summary data for days, weeks, months and seasons; in fact, this might be the easiest thing to do.
Hi James,
Your description of an interesting problem seems WAY too "general" to give useful advice. We have no idea what your data table looks like (except that it is for "several" years and each year has 8760 rows). And we have no idea of the calculations you need to do except they are "somewhat complicated."
Numbers is good at extracting summary data from subsets of raw data in a table form for specified periods of time but, again, without any specifics it's impossible to know what to suggest.
With respect to your original question, as described above, each column and each row in a Numbers behaves like a named range in Excel (and presumably VisiCalc). A table as a whole does not behave like a named range; it is a collection of these criss-crossing named ranges.
But if you have a date column in your data and want to use that to define subsets of your data on which to perform summary calculations, then SUMIFS, AVERAGEIFS and their cousins are likely to be the way to go.
Post if you wish to provide further detail.
SG
SGIII,
Thanks for your time and advice. What I thought was a simple question wasn't and led down a path I think neither of us wanted to follow too far. I'll use the summary calculations like you suggest. Not exactly what I wanted but it will do. Thanks again.
Hello
You might introduce columns for attributes you want to use in your analysis. These attribute columns will serve to identify target ranges easily.
E.g.,
DATA (excerpt) A1 date A2 2016-01-10 A3 2016-01-20 B1 d B2 =WEEKDAY(A2,2) B3 =WEEKDAY(A3,2) C1 w C2 =WEEKNUM(A2,2) C3 =WEEKNUM(A3,2) D1 m D2 =MONTH(A2) D3 =MONTH(A3) E1 s E2 =IF(D2<3,1,IF(D2<6,2,IF(D2<9,3,IF(D2<12,4,1)))) E3 =IF(D3<3,1,IF(D3<6,2,IF(D3<9,3,IF(D3<12,4,1)))) F1 value F2 =RANDBETWEEN(0,20) F3 =RANDBETWEEN(0,20)
AVG_SEASON (excerpt) A1 seanon A2 1 A3 2 B1 avg B2 =AVERAGEIF(DATA::E,A2,DATA::F) B3 =AVERAGEIF(DATA::E,A3,DATA::F)
AVG_MONTH (excerpt) A1 month A2 1 A3 2 B1 avg B2 =AVERAGEIF(DATA::D,A2,DATA::F) B3 =AVERAGEIF(DATA::D,A3,DATA::F)
Regards,
H
Hiroto, definitely a good suggestion. Thank you.
Is it possible to name a range of cells?
