What is the biggest difference?

**Siberian** · 03-06-2009, 08:07 AM

The attached sheet has two rows of numbers. The top one shows used energy, the bottom one produced energy. What I need to know is during what period of the day one has the biggest difference between the two. In this case this is between 1400 and 2000, 28 used and 9 produced (a difference of 19). The question is how this difference can be determined automatically?

**Freaky_zoid** · 03-06-2009, 09:05 AM

Hi Siberian.
I can't understand where you see the difference of 19 between used and produced.
from where I see it, you need to find the difference in every hour use and produce and then find the maximal value. Is that right?

**Siberian** · 03-06-2009, 09:42 AM

Look at the numbers in the "Used" row between 1400 and 2000. At 1400 it says 6, while at 2000 it says 34. 34-6=28. Now look at the "Produced" row. Doing the same thing here we get 29-20=9. The difference between 28 and 9 is 19. What I need is some kind of setup that can determine between what hours the difference (in this case 19) is biggest.

**Freaky_zoid** · 03-06-2009, 09:50 AM

Oh, I've got the idea... give me some time to try

**qinyg** · 03-06-2009, 11:04 AM

Check out the attached sheet. Note the produced row has been copied and pasted vertically. Don't know if that will cause any problem for you.

**Freaky_zoid** · 03-06-2009, 11:17 AM

yeah, I've come up with the same thing, the matrix, 'cos you need to find the result for each pair of hours and then compare them.
but I'm trying to find another solution that would take less place in your workbook

**zbor** · 03-07-2009, 03:53 AM

Originally Posted by Freaky_zoid

yeah, I've come up with the same thing, the matrix, 'cos you need to find the result for each pair of hours and then compare them.
but I'm trying to find another solution that would take less place in your workbook

Freaky_zoid, I'm not sure that this is working actually

but I'll put here my idea and then it need to be tested.

I thought instead of calculating with datas to calculate with area below curve. Let me show you what I thought and if this is correct whole idea can be optimised...

Sheet1.xls

I only need some matematician to see is this correct

and to compare with several datas.

(I assume that every day graph is more or less same... same shape.. only hours for maximum can go up and down)...

On chart1 you see Used and Produced energy per day...
produced is linear while used is already continuos summ of all before cases so it can get any shape but continuosly growing...

A-produced and A-used is surface below curve per each hour. So formula is (a+b)/2 * h (a, b up and down of trapeze, h is height of trapeze wich is always 1 (1 hour betwen any two values)... So surface is 0,5*(value in hour N + value in hour N+1)...

When you draw in chart2 difference between those 2 surfaces you get curve wich maximum and minimum gives you hours that are your results (14 and 20 in this case).

Now, can you see is this correct? (after that can be easily optimised of course, but I put step by step so you can follow idea)...

If there is several maximums and minimus... first max and last min should be taken... also, if minimum is before maximum that means used energy never reach produced...

**Siberian** · 03-09-2009, 02:20 AM

Wow, I want to thank everyone for their effort

zbor:
This is almost exactly what I want, only one thing is missing; once the hours (1400 and 2000) are found there should be some formula that calculates what the difference is between used and produced. In this case it it will be cell U11 subtracted from O11. If you can do this you can enjoy the fact that you have achieved perfection