macro

[gearhead]

I have an excel file with 52,560 lines in it. Each line in the
"energy" column represents energy produced in a ten-minute period.
Over the course of a year that's 52,560 ten-minute periods.
I need to get energy produced in hourly periods. In other words, add
the entries in the "energy" column in groups of six, making a new set
of 8,760 "energy" quantities, one entry for each hour in the year.
How would I do that?

 

[Jim Cone]

You can do it with a formula.
With data in column A, enter formula into B6 and fill down...

=IF(MOD(COUNTA($A$1:A6),6)=0,SUM(OFFSET(A6,-5,0,6,1)),"")

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Special Print: long columns printed side by side)




"gearhead" <[email protected]>
wrote in message
news:[email protected]...

 

[gearhead]

Thanks, Jim, it worked. After I used your macro I realized I needed
the average instead, so I changed it to average. It took much longer
for the computer to do the averaging calculations. Anyway it did, and
now I have the problem that I can't do any arithmetic operations on
the resulting entries. Apparently they are in the form of an
equation, not a number. Which is maybe why it takes the document so
long to load now. But I need to "solidify," so to speak, the entries
in that column and turn them into hard numbers. That's the only way I
know how to put it. Can you help out?
Mike

 

[Jim Cone]

For clarification, what I provided is a formula not a macro.
To replace formulas with their results...
Select the data
Right-click (any part of the selection) and choose "Copy" on the popup menu
Right-click (any part of the selection) again and choose "Paste Special"
Checkmark "Values" on the dialog displayed and click Ok.
Press the Esc key once.
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html
Review of Special Sort add-in (30 ways to sort)




"gearhead" <[email protected]>
wrote in message
Thanks, Jim, it worked. After I used your macro I realized I needed
the average instead, so I changed it to average. It took much longer
for the computer to do the averaging calculations. Anyway it did, and
now I have the problem that I can't do any arithmetic operations on
the resulting entries. Apparently they are in the form of an
equation, not a number. Which is maybe why it takes the document so
long to load now. But I need to "solidify," so to speak, the entries
in that column and turn them into hard numbers. That's the only way I
know how to put it. Can you help out?
Mike

 

[gearhead]

Thank you.

For clarification, what I provided is a formula not a macro.
To replace formulas with their results...
    Select the data
    Right-click (any part of the selection) and choose "Copy" on the popup menu
    Right-click (any part of the selection) again and choose "Paste Special"
    Checkmark "Values" on the dialog displayed and click Ok.
    Press the Esc key once.
--
Jim Cone
Portland, Oregon USAhttp://www.contextures.com/excel-sort-addin.html
Review of Special Sort add-in (30 ways to sort)

"gearhead" <[email protected]>
wrote in messageThanks, Jim, it worked.  After I used your macro I realized I needed
the average instead, so I changed it to average.  It took much longer
for the computer to do the averaging calculations.  Anyway it did, and
now I have the problem that I can't do any arithmetic operations on
the resulting entries.  Apparently they are in the form of an
equation, not a number.  Which is maybe why it takes the document so
long to load now.  But I need to "solidify," so to speak, the entries
in that column and turn them into hard numbers.  That's the only way I
know how to put it.  Can you help out?
Mike



   http://www.mediafire.com/PrimitiveSoftware