J
jawneyc
Excel Forum:
In constructing a spreadsheet that will take about 30,000 voltag
readings and calculate the electrical characterization for the system
I have embarked on a difficult task.
Essentially, the power profile for a set of data points such as this i
repetitive (system runs at 0% power then 100% power then 0% again the
back to 100%, 0%, 100%, 0%, 75%, and so on). In order to begi
calculations, one must first "map out" the power profile by goin
through the data to see which row starts the power level and which ro
ends the power level.
Once the power profile is mapped, then calculations are performed, bu
when we write out the calculations, we don't reference the map that w
made, but rather we type out all the ranges for which we ar
calculating values.
I would like to develop a spreadsheet that references the map that i
constructed for each set of data. I was going to do this by using th
ADDRESS() function to generate the ranges and then plug those range
into other functions to perform the calculations. I discovered in doin
so that you cannot embed the ADDRESS() function inside of others lik
AVERAGE().
I believe the reason for this is because of the operator I a
attempting to use. One would think that if the ADDRESS() functio
returns a text string that represents a reference, then one could us
the ':' operator between the two addresses inside of a function t
generate a value. Excel returns with an error, however, if you try t
perform something like this:
=AVERAGE((ADDRESS(J2,4))
ADDRESS(K2,4)))
I have attatched a sample file that explains my problem a little more
I would appreciate any advice any of you can offer to simplify ou
calculations a little bit more. Thank you.
-J
Attachment filename: powerdata_sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63917
In constructing a spreadsheet that will take about 30,000 voltag
readings and calculate the electrical characterization for the system
I have embarked on a difficult task.
Essentially, the power profile for a set of data points such as this i
repetitive (system runs at 0% power then 100% power then 0% again the
back to 100%, 0%, 100%, 0%, 75%, and so on). In order to begi
calculations, one must first "map out" the power profile by goin
through the data to see which row starts the power level and which ro
ends the power level.
Once the power profile is mapped, then calculations are performed, bu
when we write out the calculations, we don't reference the map that w
made, but rather we type out all the ranges for which we ar
calculating values.
I would like to develop a spreadsheet that references the map that i
constructed for each set of data. I was going to do this by using th
ADDRESS() function to generate the ranges and then plug those range
into other functions to perform the calculations. I discovered in doin
so that you cannot embed the ADDRESS() function inside of others lik
AVERAGE().
I believe the reason for this is because of the operator I a
attempting to use. One would think that if the ADDRESS() functio
returns a text string that represents a reference, then one could us
the ':' operator between the two addresses inside of a function t
generate a value. Excel returns with an error, however, if you try t
perform something like this:
=AVERAGE((ADDRESS(J2,4))
ADDRESS(K2,4)))I have attatched a sample file that explains my problem a little more
I would appreciate any advice any of you can offer to simplify ou
calculations a little bit more. Thank you.
-J
Attachment filename: powerdata_sample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=63917
,J2):INDEX(D