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 287168 times)

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 Problem
« Reply #440 on: November 26, 2015, 11:25:09 AM »
I am trying to separate and then add 2 years of hourly data to have monthly totals of peak and off peak numbers. The data comes in a .csv file formatted like this and has 17,520 rows of data.
Quote
UsageDateHourNumUsage
4/13/2013
1
58.9
4/13/2013
2
56.84
4/13/2013
3
56.22
4/13/2013
4
55.55
4/13/2013
5
55.25
4/13/2013
6
56.21
4/13/2013
7
60.69
4/13/2013
8
63.9
What I do currently is that I paste this formula through the entire column e (D is other data)
Code: [Select]
=IF(AND(B4>7,B4<22,((WEEKDAY(A4,2))<6)),"P","OP")which then fills column d with either "p" or "op" depending on the timestamp.
I paste this formula into columns f and g
Code: [Select]
=IF(E4="P",C4,"--")
Code: [Select]
=IF(E4="OP",C4,"--")This makes column f contain only peak numbers while column g contains only off peak numbers.

I then use auto filter to filter a month separately and copy paste it onto a new tab. On that tab I paste a formula to sum each column to give me the total for each month and then paste those to a separate tab where I would have all the monthly numbers.

This works but is a somewhat tedious process. Is there a simple way to get it done automatically or a simpler way to do it?
Feelings don't care about your facts

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #441 on: November 26, 2015, 12:30:55 PM »
I am trying to separate and then add 2 years of hourly data to have monthly totals of peak and off peak numbers. The data comes in a .csv file formatted like this and has 17,520 rows of data.What I do currently is that I paste this formula through the entire column e (D is other data)
Code: [Select]
=IF(AND(B4>7,B4<22,((WEEKDAY(A4,2))<6)),"P","OP")which then fills column d with either "p" or "op" depending on the timestamp.
I paste this formula into columns f and g
Code: [Select]
=IF(E4="P",C4,"--")
Code: [Select]
=IF(E4="OP",C4,"--")This makes column f contain only peak numbers while column g contains only off peak numbers.

I then use auto filter to filter a month separately and copy paste it onto a new tab. On that tab I paste a formula to sum each column to give me the total for each month and then paste those to a separate tab where I would have all the monthly numbers.

This works but is a somewhat tedious process. Is there a simple way to get it done automatically or a simpler way to do it?
Excel has a feature called Record Macro, that you can activate with a keyboard shortcut. The Macro would be saved in Excel, so it can run in any workbook.

Essentially you'd click Record, do all those formulas and steps the first time.
Then open the next workbook (or same) and tweak the run the Macro. Might need some debugging / tweaking and remove the extra clicks and steps.

The Macro would mimic all the steps and formulas you did.
« Last Edit: November 26, 2015, 12:34:05 PM by lubaby »

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #442 on: November 26, 2015, 02:03:20 PM »

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 Problem
« Reply #443 on: November 26, 2015, 02:47:13 PM »
Excel has a feature called Record Macro, that you can activate with a keyboard shortcut. The Macro would be saved in Excel, so it can run in any workbook.

Essentially you'd click Record, do all those formulas and steps the first time.
Then open the next workbook (or same) and tweak the run the Macro. Might need some debugging / tweaking and remove the extra clicks and steps.

The Macro would mimic all the steps and formulas you did.
My experience with Macro Recorder is that it makes extremely inefficient macros because it includes many more extraneous actions than those which you need. If it is for something simple and short it will work well but for something with as many steps as this it will not work well.

What I was thinking would work best would be to have a template to paste the data on which would reference everything to the correct places. The issue is that depending on when the data is pulled the start and end months vary so I cannot use simple links to cells.
Feelings don't care about your facts

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #444 on: November 26, 2015, 03:59:21 PM »
My experience with Macro Recorder is that it makes extremely inefficient macros because it includes many more extraneous actions than those which you need. If it is for something simple and short it will work well but for something with as many steps as this it will not work well.
That's why you debug it after you record it the first time. You can delete the rows of code that are extra and simplify the clicks / processing.
Macros are great for repetitive tasks, no matter how complex. It really depends how well you can "teach the computer".

Excel is a fun learning experience.

Online 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 Problem
« Reply #445 on: November 26, 2015, 04:27:51 PM »
That's why you debug it after you record it the first time. You can delete the rows of code that are extra and simplify the clicks / processing.
Macros are great for repetitive tasks, no matter how complex. It really depends how well you can "teach the computer".

Excel is a fun learning experience.
one thing you'll probably need to fix in a recorded macro is absolute/relative referencing
Workflowy. You won't know what you're missing until you try it.

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #446 on: November 30, 2015, 10:13:59 AM »
Anybody know how to return a value in a cell based on a basic =left(A9,5) function and then add on it extra few words? The point being that i want part of this cell based on the cell i am referencing but part of the cell to have a few words in their that is constant.(not based on Formula or function just plain words)

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #447 on: November 30, 2015, 10:24:28 AM »
Anybody know how to return a value in a cell based on a basic =left(A9,5) function and then add on it extra few words? The point being that i want part of this cell based on the cell i am referencing but part of the cell to have a few words in their that is constant.(not based on Formula or function just plain words)
Add
Code: [Select]
&"TEXT" to the end of your formula.

So basically,
Code: [Select]
=left(A9,5)&"TEXT"

Anything stored in between the " " would be considered a constant.

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #448 on: November 30, 2015, 10:27:42 AM »
Add
Code: [Select]
&"TEXT" to the end of your formula.

So basically,
Code: [Select]
=left(A9,5)&"TEXT"

Anything stored in between the " " would be considered a constant.
Thanks. Is there a way to put those words first before the result of the formula?

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #449 on: November 30, 2015, 10:31:49 AM »
Thanks. Is there a way to put those words first before the result of the formula?

Sure, just move around the " " tags.

Code: [Select]
="TEXT"&left(A9,5)

Offline joshnuss

  • Dansdeals Gold Elite
  • ***
  • Join Date: Apr 2014
  • Posts: 186
  • Total likes: 0
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #450 on: November 30, 2015, 10:33:22 AM »
Nice and easy Thanks

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 Problem
« Reply #451 on: November 30, 2015, 05:39:32 PM »
I am trying to separate and then add 2 years of hourly data to have monthly totals of peak and off peak numbers. The data comes in a .csv file formatted like this and has 17,520 rows of data.What I do currently is that I paste this formula through the entire column e (D is other data)
Code: [Select]
=IF(AND(B4>7,B4<22,((WEEKDAY(A4,2))<6)),"P","OP")which then fills column d with either "p" or "op" depending on the timestamp.
I paste this formula into columns f and g
Code: [Select]
=IF(E4="P",C4,"--")
Code: [Select]
=IF(E4="OP",C4,"--")This makes column f contain only peak numbers while column g contains only off peak numbers.

I then use auto filter to filter a month separately and copy paste it onto a new tab. On that tab I paste a formula to sum each column to give me the total for each month and then paste those to a separate tab where I would have all the monthly numbers.

This works but is a somewhat tedious process. Is there a simple way to get it done automatically or a simpler way to do it?

Is there a way to SUMIF all values from the most recent January out of a few years of data?
Feelings don't care about your facts

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #452 on: November 30, 2015, 05:55:37 PM »
Is there a way to SUMIF all values from the most recent January out of a few years of data?
Why not?

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 Problem
« Reply #453 on: November 30, 2015, 06:00:28 PM »
Why not?
How? and in case I wasn't clear, What I am looking for is NOT to SUMIF Jan 2015 but whichever is the most recent one in the data set
Feelings don't care about your facts

Online 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 Problem
« Reply #454 on: November 30, 2015, 07:33:13 PM »
How? and in case I wasn't clear, What I am looking for is NOT to SUMIF Jan 2015 but whichever is the most recent one in the data set
It should be doable. Do you want to post a sample file for us to work on?
Workflowy. You won't know what you're missing until you try it.

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 Problem
« Reply #455 on: November 30, 2015, 08:56:09 PM »
How? and in case I wasn't clear, What I am looking for is NOT to SUMIF Jan 2015 but whichever is the most recent one in the data set
You have to craft a condition indicating what the most recent January is. I'll post an example later when I have time.

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 Problem
« Reply #456 on: December 01, 2015, 04:29:12 PM »
It should be doable. Do you want to post a sample file for us to work on?

So in this chart I would need it to include October 2014 for the October total while in a more current data set I would need it to include Oct 2015

 
UsageDateHourNumUsage
4/14/2013
1
74.13
1/5/2014
2
77.07
10/24/2014
3
72.04
6/14/2014
4
71.66
8/19/2015
5
71.71
4/14/2014
6
72.57
4/14/2015
7
75.03
6/15/2015
8
76.15
6/30/2015
9
78.66
7/6/2015
21
82.05
7/20/2015
22
80.44
8/1/2015
23
77.37
8/5/2015
24
75.31
9/10/2015
1
73.81
9/25/2015
2
73.27
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 Problem
« Reply #457 on: December 01, 2015, 04:48:21 PM »
So in this chart I would need it to include October 2014 for the October total while in a more current data set I would need it to include Oct 2015

 
UsageDateHourNumUsage
4/14/2013
1
74.13
1/5/2014
2
77.07
10/24/2014
3
72.04
6/14/2014
4
71.66
8/19/2015
5
71.71
4/14/2014
6
72.57
4/14/2015
7
75.03
6/15/2015
8
76.15
6/30/2015
9
78.66
7/6/2015
21
82.05
7/20/2015
22
80.44
8/1/2015
23
77.37
8/5/2015
24
75.31
9/10/2015
1
73.81
9/25/2015
2
73.27
Crude version:
Code: [Select]
{=SUM($C$2:$C$16*(MONTH($A$2:$A$16)=1)*(YEAR($A$2:$A$16)=MAX(IF(MONTH($A$2:$A$16)=1,YEAR($A$2:$A$16),0))))}Make sure to ctrl-enter for array formula.

ETA: I put in 1 for January (I think that was your original example). Replace with 10 for October, or make it a reference so you can copy it down to do it once for each month of the year.

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 Problem
« Reply #458 on: December 01, 2015, 06:09:18 PM »
Crude version:
Code: [Select]
{=SUM($C$2:$C$16*(MONTH($A$2:$A$16)=1)*(YEAR($A$2:$A$16)=MAX(IF(MONTH($A$2:$A$16)=1,YEAR($A$2:$A$16),0))))}Make sure to ctrl-enter for array formula.

ETA: I put in 1 for January (I think that was your original example). Replace with 10 for October, or make it a reference so you can copy it down to do it once for each month of the year.

Thanks. I see I need to be more specific and/or clearer. I have uploaded some files to the links below.

In this file is a sample of the raw data as I receive it. I then paste it into this template to calculate which rows are for peak and which are for off-peak with this file as the result. What I am trying to do is get the total peak usage from the last 12 months to be calculated into M6:M17 and the off-peak into N6:N17. One of the criteria of a SUMIF type formula would be that Column E would say "P" or "OP". The real issue is that the dates on the data vary depending on when it was pulled and the most recent 12 months will therefore be different in each data set.

As you will see in the file, when I pasted your formula into excel (I pasted it into the formula area, removed the {}, changed the end of the arrays to be row 20000, and hit CTRL-SHIFT-ENTER) it came up as zero. Not sure if that is because I did something wrong or I was unclear about what I was looking for.
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 Problem
« Reply #459 on: December 01, 2015, 06:13:40 PM »
Thanks. I see I need to be more specific and/or clearer. I have uploaded some files to the links below.

In this file is a sample of the raw data as I receive it. I then paste it into this template to calculate which rows are for peak and which are for off-peak with this file as the result. What I am trying to do is get the total peak usage from the last 12 months to be calculated into M6:M17 and the off-peak into N6:N17. One of the criteria of a SUMIF type formula would be that Column E would say "P" or "OP". The real issue is that the dates on the data vary depending on when it was pulled and the most recent 12 months will therefore be different in each data set.

As you will see in the file, when I pasted your formula into excel (I pasted it into the formula area, removed the {}, changed the end of the arrays to be row 20000, and hit CTRL-SHIFT-ENTER) it came up as zero. Not sure if that is because I did something wrong or I was unclear about what I was looking for.
That's actually probably easier to do. I'll take a look when I get home.