Determining electric purchase and self consumption

[Mason12]

Hey Guys I have a problem with an excel task

Can somebody tell me what I should do with the task 4 ?
Open the "SLP load profile - household H0". You will find a 15-minute load profile in
Active power KW before. When importing the opening, make sure that you do not use the "comma" as a
Select separation option, otherwise the values ​​will not be imported correctly.
2. Insert a new column between B and C, this will now have the name C.
3. Click in the third row of the new column and add the date and time
together approximately = sum (A3 + B3). Now you will see a point number in the program
represents a point in time.
4. Now format the cell in a format that gives you the date and time
displays at the same time. The result looks like this for one cell (01/01/2011 00:15)
5. Click the cell (here C3). Now move your pointer to the lower right corner
the cell. A thick point will appear there. Double click on this point.
Now the previously inserted formula should have been copied to the cells below.
Check to see if this has happened. There are around 35,000 lines.

The problem is I cant post an image what I have done lol
Has somebody an idea ?

[DJunqueira]

Hey Guys I have a problem with an excel task

The problem is I cant post an image what I have done lol
Has somebody an idea ?

No image, attach your workbook.

[Mason12]

how can I post workbook?
I posted my pic to you as a private message. Perhaps you can post lol

[superjoejoe2000]

Click Column C, entire column would be selected,
Press Ctrl + 1, this will open Format Cells window,
Go to Tab Number, Category, Select Custom,
Click in TYPE box,
Type these:
mm/dd/yyyy hh:mm

Is this what you're looking for?

[DJunqueira]

See the yellow banner up written "HOW TO ATTACH YOUR SAMPLE WORKBOOK:"

[alansidman]

This appears to be a school work assignment. If not, then expand on why this is important. If this is schoolwork, then...

...Nothing is more frustrating than to be working on homework and to get stuck. Without guidance, you might have no hope of finishing the assignment before the deadline. The Internet can be a big help in that respect, but without learning how to get the right answer, asking others to solve your homework questions for you simply becomes a downward spiral (and it's cheating).

We do not want to contribute to you cheating yourself out of your education, but we also acknowledge that seeking assistance to learn a concept is a legitimate request.

If you are genuinely interested in receiving help in the form of tutoring or coaching, then please rephrase the title of your original post to clearly indicate you are seeking coaching or tutoring help. Any forum members (who are willing to assist as a tutor) will modify their responses accordingly to facilitate your learning. Tutors don't tell you the answers, they help you figure it out for yourself; so don't expect answers, expect suggestions, or just plain hints. Also, be specific in describing the function/formula or technique you trying to learn, and tell us what you have attempted so far. Otherwise, expect your plea for homework answers to be ignored.

[Mason12]

No I am learning with the task . We dont get homework . I am learning excel

[superjoejoe2000]

I agree with Alansidman the Moderator, sorry...

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[Mason12]

I cant post a pic
Otherwise I would post my current solution lol
I am still stuck in number 4

[TMS]

Again, please see the yellow banner at the top of the page.



Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

I don't think your new title will hack it …

[FlameRetired]

@ Mason12

I know that title won't hack it.

You've now heard about the title from 3 Moderators (plus a forum Guru). Please post a meaningful title.

[AliGW]

No, the new title is unacceptable.

Read post #9 again to understand what your title needs to convey. At present it tells us nothing if your issue.

[Mason12]

Here is the file

[Mason12]

Sorry I couldnt post you the link to the data

[AliGW]

We don't want links and we don't want pictures.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[AliGW]

Waiting for you to attach the workbook.

[Mason12]

I dont know how to attach my problem

[Mason12]

Now above the file

I hope somebody can also correct the problem where it show the date
ORIGINAL TXT IS THIS

[alansidman]

@Mason
You have been asked several times to change your title to one that describes what you wish to happen. Please comply as this is important to searching by others.

No further help for this thread until this request to change the title has been satisfied.

[Mason12]

Then tell me which title you name want and I will change it lol

[TMS]

help solution calculation

No, no, no, no, no.

Describe the problem. What does question 4 say?

Is this homework? Sounds like a homework question.

[AliGW]

Then tell me which title you name want and I will change it lol

You have to do that. Your title needs to be a short DESCRIPTION if what you are trying to do. Use the wording of task 4 to help you formulate this.

Once that is done, attach your EXCEL WORKBOOK.

[bsalv]

it's for school !!!
Just import the text-file and in my excel365, it creates immediately a table and then a little bit playing around (with for example a pivottable).

The excel-file is 1.4 MB, too big to upload.

[Mason12]

Thats now the task which I am dealing with

Forgot to post lol
Open the load profile "PV system Kronberg" and edit the
Load profile analogous to step 1 of this exercise. The goal is next to that
To display a PV load profile directly. Note that
the time stamp and day stamp should / must match. The year is in
negligible in this case. If individual values ​​are missing or offset,
adjust your PV load profile sensibly. (An offset in the load profiles can
due to logging problems or due to an offset winter-summer-winter time changes.)
2. Now scale the PV load profile in the adjacent column to that of
You determined kWp power of your PV system. The present load profile
"Kronberg PV system" belongs to a 4.51 kWp PV system.
3. Now determine the load profiles for the in the adjacent columns
“Electricity purchase”, “self-consumption” and “feed-in”. Under the assumption,
You would have no electricity storage and no heat pump. (Tip: take advantage of
including the arithmetic operations in Excel such as "= if ()" and the
Comparison symbols <,> = etc.)
4. Then determine the consumption or feed-in sums of the
individual columns. The table should now look something like Figure 2
look.


2 and 4 please ?
What should I do

[bsalv]

i have no clue

[alansidman]

I am closing this thread. Mason12, you can PM any one of the moderators in this thread with a satisfactory title and if that mod agrees, then they will reopen the thread. Until that happens, this thread is closed.

[alansidman]

OP has provided a satisfactory Title and I have changed it for him. This thread is now open

[Mason12]

thanks now I hope perhaps for help

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook. This will be a cut down copy of the full workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Mason12]

Hi,
I think it doesnt really matter what I upload now because nobody really knows what to do

I am currently only uploading with no response

[AliGW]

nobody really knows what to do

How rude! Don't blame the membership for your own shortcomings - that won't help your cause.

I am currently only uploading with no response

Still no workbook ... What do you expect?

[bsalv]

indeed, rude.
Where can i find this ?

Open the load profile "PV system Kronberg"

[Mason12]

No it wasnt rude .

[bsalv]

it's a mess, i don't know where you want it, but it's something like this

* Electricity purchase = max(0,consumption-production)
* self-consumption =min(production,consumption)
* feed-in =max(0,production-consumption)

[Mason12]

It should look so in the end . I will post a pic

max(0,consumption-production) ?
Should I write that in the excel programm ?

Could you correct the problem with the date which I am getting lol?
like 5.07.2011 21.45 ?
Because my sum of the Watt is not correct sadly
Thats a pic how it should look like

Attachment 760266

[bsalv]

i had to delete a lot of rows to be able to upload this file, but you can see how it works
with 2.000 kwh consumption and 946 PV-production, you purchase 1.595 kwh and inject 540 Kwh.

[Mason12]

Can you send me the file with all numbers because with copy and paste it shows again errors lol .
My bad computer

You can send it via mail

[Mason12]

Has somebody else an Idea . I am still stuck with the calculation lol

[JeteMc]

Can you send me the file with all numbers because with copy and paste it shows again errors

I imagine that is because the range A13:F35052 in the .xlsx file attached to post #35 is not a table so the structured references formulas don't work.
Try applying the following to the .xlsx file attached to post #35:
Cells G13 and down: =C13
Cells H13 and down: =F13/100
Cells I13 and down: =MAX(0,G13-H13)
Cells J13 and down: =MIN(G13,H13)
Cells K13 and down: =MAX(0,H13-G13)
Cells L13 and down: =G13-H13-I13+K13
Let us know if you have any questions.

[Mason12]

hi can you correct it in the data ?
I am not getting how to do it lol

I think the claculation will be made like you posted ?
But the problem is tat some values are coming as date lol

[JeteMc]

File from post #35 with the formulas applied to columns G:L
I will have to delete a lot of rows in order to get the file to load.
Not able to get a .zip file of smaller than 2.11 MB with all rows of data and that will not load.
This is very frustrating as according to the Manage Attachments sheet a .zip file up to 9.77 MB should load.

[Mason12]

you can send it via mail
I send you pn

[Mason12]

Guys The last task was so :1. As in step 1 of this exercise, import the load profile “Heat pump
- Interruptible ".
2. As in step 2, adapt the load profiles to one another by pressing the
Check timestamp.
3. Scale this load profile as well. This time on the consumption of your
Heat pump. This model load profile of the heat pump is on one
Annual consumption scaled from 1000 kWh. Please note that this
Load profile was not provided in kW but in kWh. You need to
thus convert the individual values into kW. Otherwise you can do this
Do not offset the load profile directly with the others. You can carry out the kW conversion directly during scaling by entering the values
of the cells always multiply by 4 (reason: 15 min. load profile).
4. Now determine the load profiles for the in the adjacent columns
“Electricity purchase”, “self-consumption” and “feed-in”. This time under the
Assumption you still don't have an electricity storage system, but this time one
Heat pump in operation.
5. Then determine the consumption or feed-in sums of the
individual columns. The table should now look something like Figure 3
look.
6. Link the relevant results of the load profiles with yours
Profitability calculation.

Is the calculation again the same ?
We have only this file called unberechenbar = interruptible

The file unberechenbar is this:

Has somebody an Idea about the calculation?

[Mason12]

Does somebody understand :
3. Scale this load profile as well. This time on the consumption of your
Heat pump. This model load profile of the heat pump is on one
Annual consumption scaled from 1000 kWh. Please note that this
Load profile was not provided in kW but in kWh. You need to
thus convert the individual values into kW. Otherwise you can do this
Do not offset the load profile directly with the others. You can carry out the kW conversion directly during scaling by entering the values
of the cells always multiply by 4 (reason: 15 min. load profile).


How I can calculate this scale value ? I am not getting it

[Mason12]

How could I write such an calculation in my excel file ?
With this x>0 ?
X = Power_PV_Anlage - self-consumption

If X> = 0, then

Electricity purchase = 0
Mains supply = X

If X <0 then is

Electricity purchase = -X
Mains feed-in = 0

[JeteMc]

It would help if we could see an .xlsx file so that we can see the actual setup, however this is what I imagine:
Four column headers: Power_PV_Anlage (A1), self-consumption(B1), Electricity purchase(C1) and Mains supply(D1)
Columns A:B would be populated with values.
Column C would be populated using: =IF(A2-B2>=0,0,-(A2-B2))
Column D would be populated using: =IF(A2-B2>=0,A2-B2,0)
Let us know if you have any questions.

[Mason12]

Has somebody an Idea how I should write the calculation in the file?

[JeteMc]

Attached is how I interpret #2 (post #45).
Compare the file attached here to the file attached to post #45.
I feel that the numbers in column E are the kWh and if so then I believe step #3 is saying that those need to be multiplied by 4.
If you are simply trying to learn Excel as stated in post #7, there are courses that in my opinion are easier to follow than this, such as "Excel is Fun" which is taught by one of Highline College's instructors, Mike Girvin (link to introductory video).

[Mason12]

Can you tell how the other calculations can be done ?
Were my calculations right?

[JeteMc]

I have no idea about numbers 4 and 6 (post #45) and believe that they are meant for someone that has a prior understanding of load profiles of heat pumps.
Number 5 references a "Figure 3" which suggests to me that there is more to this than what we are able see and therefore I don't feel that I can help.
Again, I suggest trying to learn Excel using the "Excel is Fun" classes.
The lessons that I have seen include both the starter file for the student to work, and the answer file so that the student can check her/his work.