Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

Author Topic: Excel Help and Problems  (Read 287137 times)

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1360 on: March 03, 2020, 06:25:46 PM »
How can I get half-hourly data which looks like this
 
2/1/2019 0:30
402.48
2/1/2019 1:00
399.6
2/1/2019 1:30
392.4
2/1/2019 2:00
386.64
2/1/2019 2:30
374.4
2/1/2019 3:00
373.68
2/1/2019 3:30
365.04
2/1/2019 4:00
369.36
2/1/2019 4:30
381.96
converted into hourly data formatted like this
11/1/2018
1
166.32
11/1/2018
2
147.96
11/1/2018
3
144.36
11/1/2018
4
146.16
11/1/2018
5
153.72
11/1/2018
6
170.64
11/1/2018
7
176.04
11/1/2018
8
179.28
11/1/2018
9
178.56
11/1/2018
10
164.16
11/1/2018
11
130.68
11/1/2018
12
138.24
11/1/2018
13
135
or this
 
11/1/18 1:00
166.32
11/1/18 2:00
147.96
11/1/18 3:00
144.36
11/1/18 4:00
146.16
11/1/18 5:00
153.72
11/1/18 6:00
170.64
11/1/18 7:00
176.04
11/1/18 8:00
179.28
11/1/18 9:00
178.56
11/1/18 10:00
164.16
11/1/18 11:00
130.68
11/1/18 12:00
138.24
11/1/18 13:00
135
Try:
=MROUND(A1,1/24)
Change A1 to the cell containing your data, and double click the fill handle to repeat the formula for all your instances.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1361 on: March 03, 2020, 08:36:10 PM »
What are those numbers following the time? And are the in the same cell?
One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.
Feelings don't care about your facts

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1362 on: March 04, 2020, 12:50:56 AM »
One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.
https://www.excelcampus.com/charts/group-times-in-excel/

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1363 on: March 04, 2020, 07:33:18 AM »
https://www.excelcampus.com/charts/group-times-in-excel/
These examples are counting transactions. The more similar example with their data would be to get the sum of the dollar amount per hour. I was able to get hours summed together using PivotTable but it is grouping the hours differently from the way the labeling intends. It also doesn't give the date and time back in the way I need it. I am going to try playing around with the concepts here though. Thanks!
Feelings don't care about your facts

Offline the great

  • Dansdeals Silver Elite
  • **
  • Join Date: Jun 2010
  • Posts: 51
  • Total likes: 4
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Help and Problems
« Reply #1364 on: March 04, 2020, 08:35:46 AM »
Anyone know if it’s possible to make part of a formula to come out underlined?

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1365 on: March 04, 2020, 09:28:23 AM »
Anyone know if it’s possible to make part of a formula to come out underlined?
I believe not, unless maybe with VBA
Workflowy. You won't know what you're missing until you try it.

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1366 on: March 04, 2020, 10:10:48 AM »
One cell is date and time the other is the value. I don't care if in the end result the date and time are separated as long as the end result has the total value of each hour added together.

I'm not sure what those values are, but if you can separate them from the date & timestamp, the below should work. (use Data>Text To Columns)


Try:
=MROUND(A1,1/24)
Change A1 to the cell containing your data, and double click the fill handle to repeat the formula for all your instances.

Just make sure that your cells are formated correctly (under Format Cells> Number tab> Data Section> Choose the type that returns d/mm/yy 00:00).

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1367 on: March 04, 2020, 03:03:56 PM »
I'm not sure what those values are, but if you can separate them from the date & timestamp, the below should work. (use Data>Text To Columns)


Just make sure that your cells are formated correctly (under Format Cells> Number tab> Data Section> Choose the type that returns d/mm/yy 00:00).
It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.
Feelings don't care about your facts

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1368 on: March 04, 2020, 03:11:30 PM »
It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.
Sorry, don't know what those values represent.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1369 on: March 04, 2020, 03:18:15 PM »
Sorry, don't know what those values represent.
They represent the amount of kWh of electricity used over that half hour.
Feelings don't care about your facts

Offline SrulyS

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2015
  • Posts: 602
  • Total likes: 68
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1370 on: March 04, 2020, 03:50:53 PM »
It seems to me that you think I am trying to change the 30 hours to be rounded up and do not care about the values. That is not the case. I am looking for a sum of the values for each hour.

You're trying to eliminate the half hour rows while adding their values (from the rightmost column) to the row above/below?

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1371 on: March 04, 2020, 03:57:08 PM »
Anyone know if it’s possible to make part of a formula to come out underlined?
Possible with VBA only

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1372 on: March 04, 2020, 04:03:20 PM »
They represent the amount of kWh of electricity used over that half hour.
Ok. Got it now.
2 options:
1) follow my instructions as before, copy paste values so that excel actually changes the half hour values to hourly, and use a pivot table to sum for each hour
2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1373 on: March 04, 2020, 05:53:26 PM »
You're trying to eliminate the half hour rows while adding their values (from the rightmost column) to the row above/below?
Correct.
Feelings don't care about your facts

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17395
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1374 on: March 04, 2020, 05:56:33 PM »
Ok. Got it now.
2 options:
1) follow my instructions as before, copy paste values so that excel actually changes the half hour values to hourly, and use a pivot table to sum for each hour
2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.
These are the directions I have been going. Pivot table does it without needing to change the half hour values. I need to tweak it though to group propery and to align them with a date and time cell to input properly.
2 is what I have been thinking but didn't test yet. I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.
Feelings don't care about your facts

Offline cgr

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Aug 2017
  • Posts: 4377
  • Total likes: 1318
  • DansDeals.com Hat Tips 7
    • View Profile
Re: Excel Help and Problems
« Reply #1375 on: March 04, 2020, 06:07:32 PM »
These are the directions I have been going. Pivot table does it without needing to change the half hour values. I need to tweak it though to group propery and to align them with a date and time cell to input properly.
2 is what I have been thinking but didn't test yet. I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.
For the fill handle, fill in the first 4 instances yourself, and then excel will automatically understand what you mean when you double-click it to fill down.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1376 on: March 04, 2020, 06:17:43 PM »
Or you  can wrap your function in IF(MOD(celladdress,2)=0,restofformula,"") to return blank if the cell row # is odd (or adjust it for even rows)
Workflowy. You won't know what you're missing until you try it.

Offline SrulyS

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2015
  • Posts: 602
  • Total likes: 68
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1377 on: March 04, 2020, 07:08:03 PM »
I have not been successful in getting the fill handle to fill only every other row, but I thought of some work arounds which I did not get to yet.
For the fill handle, fill in the first 4 instances yourself, and then excel will automatically understand what you mean when you double-click it to fill down.
Or you  can wrap your function in IF(MOD(celladdress,2)=0,restofformula,"") to return blank if the cell row # is odd (or adjust it for even rows)

Just enter the formula once, select formula and one blank cell, and then fill down, it will only fill every other row.

2) separate date/time value and kwh into 2 columns, sum every second row (use fill handle), copy paste summed values (text only- not formulas) back into original kwh column, sort so that data with empty columns can be easily deleted.

I was going to suggest something similar but (I believe) a little easier.  If you are doing this regularly I suggest using keyboard shortcuts and with my method you will not need to switch selected range once you fill down the formula so it should be quicker. Once you get used to this shortcut sequence it will be really quick.

Sum every second row (make sure to put formula in x:00 rows,and if you want 1:30 with 1:00 make sure to sum the proper rows, etc..). Just enter the formula once, select formula and one blank cell, and then fill down, it will only fill every other row.

Then with the cells still selected you can do the following:
Ctrl+C
Left Arrow
Alt+H, V, V                        (Paste Values in original column)
Alt+H, FD, S, K, Enter       (Select blank cells from current selection [if you are doing this without shortcuts, it's Find & Select, Go to Special, Blanks])
Alt+H, D, R,                     (Delete rows of selected cells)
Right Arrow
Alt+H, D,C                        (Delete formula column)  [Ctrl+Space, Ctrl+Minus is probably better for this step]

Offline Der Deutsche Jude

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Jan 2016
  • Posts: 934
  • Total likes: 46
  • DansDeals.com Hat Tips 4
    • View Profile
  • Location: Brooklyn, NY
  • Programs: AA, CA, FAA, GA, NA, OA, WA
Re: Excel Help and Problems
« Reply #1378 on: March 05, 2020, 11:39:47 PM »
Hi all, anyone with familiarity with Excel Templates that has a solution to this problem?

I have a Word document with a hyperlink to an Excel Template (.xltx) but when clicked, it opens the original file, not a new instance of it. How do I get it to open the same as when you would open it from the file browser or from a shortcut?

Thanks.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1379 on: March 06, 2020, 12:29:16 AM »
Maybe try saving the template as read-only?
Workflowy. You won't know what you're missing until you try it.