Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

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

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11444
  • Total likes: 1469
  • 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.

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2240
  • Total likes: 384
  • DansDeals.com Hat Tips 4
    • 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: 11039
  • Total likes: 4833
  • DansDeals.com Hat Tips 8
    • 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.
Just because things turned out a certain way doesn't mean you were right.

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2240
  • Total likes: 384
  • DansDeals.com Hat Tips 4
    • 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: 11039
  • Total likes: 4833
  • DansDeals.com Hat Tips 8
    • 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.
Just because things turned out a certain way doesn't mean you were right.

Offline SrulyS

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2015
  • Posts: 602
  • Total likes: 59
  • 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?

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2240
  • Total likes: 384
  • DansDeals.com Hat Tips 4
    • 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

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2240
  • Total likes: 384
  • DansDeals.com Hat Tips 4
    • 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: 11039
  • Total likes: 4833
  • DansDeals.com Hat Tips 8
    • 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.
Just because things turned out a certain way doesn't mean you were right.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 11039
  • Total likes: 4833
  • DansDeals.com Hat Tips 8
    • 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.
Just because things turned out a certain way doesn't mean you were right.

Online cgr

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2017
  • Posts: 2240
  • Total likes: 384
  • DansDeals.com Hat Tips 4
    • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11444
  • Total likes: 1469
  • 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: 59
  • 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: 855
  • Total likes: 22
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 11444
  • Total likes: 1469
  • 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.