Finding area of cyclic loops from raw data

[agrwl.anmol]

Dear all,


(please refer to the attachment)

I have raw data for hysteresis loops for approximately 100 cycles (from 0% to 300%).

I want to estimate hysteresis % for each loop (or cycle), which is area of the loop.

How can I do it in the most effective way? Looking for help!


Kind regards,
Anmol

[Glenn Kennedy]

Is there any possibility of getting a response to the solutuon offered in your other live question?

[Glenn Kennedy]

I have no idea what a hysteresis loop is. Ho would you calculate the results manually? Can you do so for a couple of cycles, so that we can verfiy the results?

[agrwl.anmol]

Thanks for your response

Yes, I will do it and get back to you in a while.

[agrwl.anmol]

Please find the attached file.


First cycle is from row 3 to row 289 (or row 290)

I put it separately in columns Q and R.

Then, I separate upward and downward cycle (cell Q143 is the point of separation).

Upward cycle is columns S and T and downwards is U and V. Then I plot the graph with upward = blue and downwards = orange.

Hysteresis = (area under blue - area under orange)/area under blue which will be around 0.12 or something (but i do not know how to find it).

Then, the second cycle is from row 291 to 581 (from 0% to 0%) and so on.


Please let me know if you have any questions. Any help will be really appreciated!


Kind regards
Anmol

[Glenn Kennedy]

Work in progress.

I make it 0.131.... so we're on the right line (see yellow cells). Basically the formula has calculated the sum of the areas of the individual trapezia between successive points. Does this look correct to you?

The next bit. Are the starting point(s) of each loop fixed or variable? The first data set is 287 points. Whatabout the 2nd 3rd, etc?

[agrwl.anmol]

WOW! I was trying to make the x-axis data same to get a difference so that I can integrate them, but couldn't do so. That is amazing! Thank you very much...

Also, the starting points seem to be the same. I cross checked it. For example, the second loop starts from 289, third from 581 and so on. The gap is always 292 data points or so. I mean every loop have same number of data points.

Can this be of any help to produce results in column O (hysteresis values)?

[MrShorty]

It probably depends on exactly how you want to approach this sort of thing, but I think the easiest approach is a basic Riemann sum from the tabulated data. If you have forgotten Riemann sums from your calculus classes: https://calculushowto.com/problem-solving/riemann-sums/

For example, a simple left-hand rule might be implemented like this:
0) Detect and maybe extract a single loop with its up and down sections. Since your example already includes an extracted loop, I assume you already know how to do this.
1) In W2, I enter the formula to calculate the area of the first slice =T2*(S3-S2). Note the relative references, then copy/paste/fill down to the 2nd to last entry for the up part of the loop (W142). The total area is just the sum of the areas, so I enter a SUM() function in X2 =SUM(W2:W200)
2) To get the down portion, I copy W2 into Y2, then copy Y2 down to the bottom of the down data (Y145). Then copy X2 into Z2 to get the total area under the down curve.
3) Your formula for hysteresis is then a simple =(X2-Z2)/X2. I get about 13%.

Similar procedures will give your a right hand sum. If you get both left and right hand sums, trapezoid rule is simply the average of the LHS and RHS. Other variations as you deem appropriate. With tabulated data like this, that's probably how I would do it, unless you have a different method that you prefer (in which case, help us understand the method you like to use and we can help you program that method into the spreadsheet).

Edit to add: It looks like Glenn beat me to it, and went all the way to the trapezoid rule.

[Glenn Kennedy]

OK. Step 2...

As a next step, I have assumed that there is a 290 row cycle. for every rw the formula is copied down, the range shifts by 290 rows. The next (and hopefully final) stage is to verify the value of 290. I am still unsure of the degree of accuracy that is needed to define the successive maxima. Certainly, they will have the potential to introduce a considerable error into the results, because of their disproportionate contribution to the AUC.

Might need some more caffeine at this point...

[Glenn Kennedy]

Mr S... any bright ideas about defining successive maxima/minima? I was hoping that you'd drop by!!

[MrShorty]

As with other signal processing type problems, I often find that change/rate of change can be an indicator. For the OP's data, a column with something as simple as =A4-A3 copied down would show by change of sign when the x values were increasing and when they were decreasing. Your formula/algorithm for detecting up/down portions would then look at this column for changes of sign. I would rarely if ever try to put this into a single cell formula, though.

[agrwl.anmol]

Dear Glenn,

Thanks for the step 2.

The values for the first 5 cycles look okay, but then something goes wrong. Theoretically, the values should converge to 4.5 or 4 or less, following a graph of y = e^(-x) kind of.

Let me look into the data to confirm that there are actually 290 data points for each cycle.


Kind regards,
Anmol

OK. Step 2...

As a next step, I have assumed that there is a 290 row cycle. for every rw the formula is copied down, the range shifts by 290 rows. The next (and hopefully final) stage is to verify the value of 290. I am still unsure of the degree of accuracy that is needed to define the successive maxima. Certainly, they will have the potential to introduce a considerable error into the results, because of their disproportionate contribution to the AUC.

Might need some more caffeine at this point...

[Glenn Kennedy]

It's getting out of sync with itself...

[agrwl.anmol]

Dear MrShorty,


Thanks for your comment. I am aware of Reimann sum but did not know how tot use it here.
I have used it earlier in excel though, i one of my previous posts.

The value coming from your solution is quote accurate, but separating each and every cycle and then calculating will be tedious.

As mentioned in the last comment, I will check into the symmetry of the data so that values can simply be dragged down.


Kind regards,
Anmol

It probably depends on exactly how you want to approach this sort of thing, but I think the easiest approach is a basic Riemann sum from the tabulated data. If you have forgotten Riemann sums from your calculus classes: https://calculushowto.com/problem-solving/riemann-sums/

For example, a simple left-hand rule might be implemented like this:
0) Detect and maybe extract a single loop with its up and down sections. Since your example already includes an extracted loop, I assume you already know how to do this.
1) In W2, I enter the formula to calculate the area of the first slice =T2*(S3-S2). Note the relative references, then copy/paste/fill down to the 2nd to last entry for the up part of the loop (W142). The total area is just the sum of the areas, so I enter a SUM() function in X2 =SUM(W2:W200)
2) To get the down portion, I copy W2 into Y2, then copy Y2 down to the bottom of the down data (Y145). Then copy X2 into Z2 to get the total area under the down curve.
3) Your formula for hysteresis is then a simple =(X2-Z2)/X2. I get about 13%.

Similar procedures will give your a right hand sum. If you get both left and right hand sums, trapezoid rule is simply the average of the LHS and RHS. Other variations as you deem appropriate. With tabulated data like this, that's probably how I would do it, unless you have a different method that you prefer (in which case, help us understand the method you like to use and we can help you program that method into the spreadsheet).

Edit to add: It looks like Glenn beat me to it, and went all the way to the trapezoid rule.

[Glenn Kennedy]

Your data are symmetrical... apart from the fact that the data should (I think) start on row 5 (with 4 blank rows above it).

The cycle is 146 between mid-point maxima and minima. The formulae have been ajusted to reflect that and tthe values are converging.

If you have more datasets to analyse the "trick" (skill??) will be in judging if there are "missing rows" at the start and if so, how many?

Hw does v3 look??

[MrShorty]

As I told Glenn, will dx (=A4-A3 or SIGN(A4-A3) copied down) be an adequate indicator of "next segment"? In your sample, this gives a column of positive and negative numbers. There are usually 1 or more 0 values in between the changes in sign. Will that be enough to detect "begin/end of segment", or is there more needed for detecting begin/end of segment?

The generic idea is to have a column that computes change or slope or similar. Then a column that checks the change/slope column for changes in sign and "marks" each segment =IF(change column indicates new segment,new segment number,old segment number). Details of each argument depend on the specific behavior of your data. As an example:

1) compute a dx column =A4-A3 copied down. (Assume this is in C3 and copied down)
2) To make the changes in sign more distince, take a 3 cell moving average and take its sign =SIGN(AVERAGE(C5:C3)) in D4 and copied down.
3) Mark each segment. In E4, enter =IF(D3*D4<1,E3+1,E3) and copy down. Now column E "marks" each segment with an integer. Odd numbers will be up segments and even numbers will be down segments.

Now if I had additional columns with the areas for each slice, my integrals could be SUMIFS() functions using column E as a criteria argument =SUMIFS(column of slice areas,$E$4:$E$4000,1) would tally up the area of the first up segment.

Something like that might work. The difficult part is knowing enough about the behavior of your signal to choose appropriate change/slope functions and the detection step to properly detect begin/end of segment.

[agrwl.anmol]

Dear Glenn,


PERFECT! That solves almost everything! My raw data was originally for 200 cycles, but I had to cut down to 100 cycles to keep the file within 1 MB.

Now I tried with the same sample with all 200 cycles, and it works like a charm! I just extrapolated the data to 200 cycles. (see attachment)

Thank you sooo much really for sorting it out so quickly!!


Lastly, I have around 10 samples in total and the raw data for them has different number data points.
I tried using the same template for a sample which had 1 data point more overall (58398 data points in total as compared to 58397 which the attached file has). It worked fine and I got the expected result.

But then I tried on another one which has 120 data points less (58387 in total) and the graph went haywire.


How can this last thing be worked upon? Please see the attachment.


Kind regards,
Anmol

Your data are symmetrical... apart from the fact that the data should (I think) start on row 5 (with 4 blank rows above it).

The cycle is 146 between mid-point maxima and minima. The formulae have been ajusted to reflect that and tthe values are converging.

If you have more datasets to analyse the "trick" (skill??) will be in judging if there are "missing rows" at the start and if so, how many?

Hw does v3 look??

[Glenn Kennedy]

Away from my PC for the night. Back tomorrow.

[Glenn Kennedy]

Bummer... The problem is simple... the solution... I do not know (yet). The number of datapoints per cycle is NOT uniform. It oscillates a bit between having 2 rows at the MAX/MIN and 3 rows at the MAX/MIN. The foumula gets out of balance and, a bit like an unbalanced centrifuge, goes walkabout.

I sort of knew this would happen. It'll need a more robust way of defining the sart/end of successive datasets.

Glenn reaches for the espresso machine.

[agrwl.anmol]

Dear Glenn,


Thank you for the message. This is indeed true (unsymmetrical data). I was thinking if its possible to replace all 2 or 3 0% with one 0%, and then use an 'IF' condition which finds 0% from one to the next and return the values. Just a thought to simplify it.

I hope espresso helps you find a better solution!


Kind regards,
Anmol

Bummer... The problem is simple... the solution... I do not know (yet). The number of datapoints per cycle is NOT uniform. It oscillates a bit between having 2 rows at the MAX/MIN and 3 rows at the MAX/MIN. The foumula gets out of balance and, a bit like an unbalanced centrifuge, goes walkabout.

I sort of knew this would happen. It'll need a more robust way of defining the sart/end of successive datasets.

Glenn reaches for the espresso machine.

[Glenn Kennedy]

Just back to look at it again.

[Glenn Kennedy]

OK. Sometimes we have 2 points and sometimes we have 3 at the MAX or MIN. Is it a possibility to demarcate between the series at the FIRST occurrence of EITHER the MAX or MIN??? I so, then we might be there..

[agrwl.anmol]

Dear Glenn,


I am not quite sure how to do that (I mean which function to use for the same).


Kind regards,
Anmol

[Glenn Kennedy]

No. I'll do it. Is it OK for you - you know what you're trying to achieve - and what consequence might flow if you/me get it wrong!! I'm just fooling around with a pile of numbers..

[agrwl.anmol]

Dear Glenn,


Good morning! Any success?


Kind regards,
Anmol

No. I'll do it. Is it OK for you - you know what you're trying to achieve - and what consequence might flow if you/me get it wrong!! I'm just fooling around with a pile of numbers..

[Glenn Kennedy]

No!! I'm out for a few hours. I'm 99% there. The last 1% is being a pain in the @$$.

[agrwl.anmol]

Haha okay! Waiting eagerly for the solution!

No!! I'm out for a few hours. I'm 99% there. The last 1% is being a pain in the @$$.

[Glenn Kennedy]

This is not there yet, but is almost, almost there. AT least (I think) I know what I'm doing now!!!

1. Take a look and see how close it is to meeting your expectations.

2. I expect it will not quite be there yet. You didn't answer my Q before, so I'll rephase it.

Defining the minimum point is of relatively minor importance, as it will contribute little to the AUC. However, if you don't define where the MAXIMUM is there can be quite an effect on the AUC. There may be 1, 2 or 3 points at the maximum (300%).

Which value(s) at 300 % count towards the ASCENDING part of the curve? Which values at 300% contribute to the DESCENDING part of the curve. Bear in mind that there may be 1, 2 or 3 points at the MAX. See book1.xlsx

Sheet attached is still a work in progress until you understand my Q... and until I understand your A!!

[agrwl.anmol]

Dear Glenn,

Please see my answers in the file.

Kind regards,
Anmol

This is not there yet, but is almost, almost there. AT least (I think) I know what I'm doing now!!!

1. Take a look and see how close it is to meeting your expectations.

2. I expect it will not quite be there yet. You didn't answer my Q before, so I'll rephase it.

Defining the minimum point is of relatively minor importance, as it will contribute little to the AUC. However, if you don't define where the MAXIMUM is there can be quite an effect on the AUC. There may be 1, 2 or 3 points at the maximum (300%).

Which value(s) at 300 % count towards the ASCENDING part of the curve? Which values at 300% contribute to the DESCENDING part of the curve. Bear in mind that there may be 1, 2 or 3 points at the MAX. See book1.xlsx

Sheet attached is still a work in progress until you understand my Q... and until I understand your A!!

[Glenn Kennedy]

I think we will have to remove the multiple zeros and 300s as you suggested. I'm not immediately sure how to do that with 60000 rows. Mmmm. Food for thought.

[Glenn Kennedy]

Eventually, I got there (with help from Saravnepali):

https://www.excelforum.com/excel-pro...-averages.html

Take a look. Enable macros. Click Calculate. Wait about 30 seconds... Voila??!!

Does this look right? I can walk you through it later.

[agrwl.anmol]

Dear Glenn,


Firstly, I would like to sincerely thank you for helping me to this extent. I have successfully evaluated the results for all my samples using your template. It works like a magic! (though I'm not sure how ).

Also, thank you @Saravnepali for help!

I was caught with with work and hence could not check the template earlier.

Have a nice day!!

Kind regards,
Anmol

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[agrwl.anmol]

Hi Glenn,


For my calculations, I had to change data to 400% (from 300%). That resulted in more data points. When I tried to use the previous template, it didn't work, even after i changed the formula cell nos from 69999 to 80000 to increase the limit. What can be done? I am attaching raw data here.

I had to reduce the data size to 1Mb, but original number of rows were around 77000.


Kind regards,
Anmol

[agrwl.anmol]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Hi Glenn,

Could you please reply to my above post?

Kind regards,
Anmol