Go Back  PPRuNe Forums > PPRuNe Social > Jet Blast
Reload this Page >

spreadsheet problem

Jet Blast Topics that don't fit the other forums. Rules of Engagement apply.

spreadsheet problem

Old 26th Mar 2020, 12:18
  #1 (permalink)  
Thread Starter
 
Join Date: Sep 2004
Location: Berkshire, UK
Posts: 673
spreadsheet problem

I generated a spreadsheet yesterday in order to figure out something I can't do in my head. I got to the end of adding together all of the numbers in a column and need to take the square root of the value. Libreoffice calc doesn't seem able to perform this task if the summation is left as a formula. If I convert the summation box to a number rather than being a formula the square root works without error using =E72^0.5. After doing this anything I change earlier in the sheet fails to ripple through past this point.

Any ideas how I to get past this?

Rans6............
rans6andrew is offline  
Old 26th Mar 2020, 12:37
  #2 (permalink)  
c52
 
Join Date: Sep 2007
Location: Surrey
Posts: 1,443
Works perfectly for me doing what you describe. Does E72 change correctly when you alter the numbers in the column?
c52 is offline  
Old 26th Mar 2020, 12:40
  #3 (permalink)  
 
Join Date: Nov 2001
Location: west yorks
Posts: 38
Originally Posted by rans6andrew View Post
I generated a spreadsheet yesterday in order to figure out something I can't do in my head. I got to the end of adding together all of the numbers in a column and need to take the square root of the value. Libreoffice calc doesn't seem able to perform this task if the summation is left as a formula. If I convert the summation box to a number rather than being a formula the square root works without error using =E72^0.5. After doing this anything I change earlier in the sheet fails to ripple through past this point.

Any ideas how I to get past this?

Rans6............
Not sure that I understand this.
In Excel if I have a column of figures and create an autosum in, for example cell F9, the square root can be created in another cell by using =sqrt(F9)

The square root automatically changes with any subsequent changes to the column, even if the figures in the column are themselves the product of another calculation.
Hope that helps. No idea if LibreOffice works differently

dbromle is offline  
Old 26th Mar 2020, 12:40
  #4 (permalink)  
c52
 
Join Date: Sep 2007
Location: Surrey
Posts: 1,443
Have just reread exactly what you wrote. Wait a sec.
c52 is offline  
Old 26th Mar 2020, 12:42
  #5 (permalink)  
c52
 
Join Date: Sep 2007
Location: Surrey
Posts: 1,443
This works for me using a slightly old version of LibrOffice

=(SUM(J3:J369)^0.5)

have you got enough brackets?
c52 is offline  
Old 26th Mar 2020, 13:00
  #6 (permalink)  
Thread Starter
 
Join Date: Sep 2004
Location: Berkshire, UK
Posts: 673
Interestingly it is working for me here, this morning. Yesterday it failed with an error when selecting the E72 by mouse clicking on it. Even SWMBO, who plays spreadsheets more than I do failed to make it happen. There is a quirk somewhere in the system. Perhaps saving it and closing the machine down and restarting has cleared it..... That will bug me now, if it can't be relied upon.

What I am playing with is trying to measure true RMS AC power using a current transformer and voltage connection on our household mains. The readings need to be crunched in a PIC micro programmed in assembler and really need to work in real time. The PIC I am using has a hardware multiplier but no quick or easy way to calculate square roots. I think I can make the calculation of Vrms * Irms on multiple samples per cycle of the mains without actually doing any square root calculation. The end result is to enable me to switch stuff on dynamically whenever our solar panels produce enough excess power to run each item of stuff.

Thanks for trying.

Rans6
rans6andrew is offline  
Old 26th Mar 2020, 13:04
  #7 (permalink)  
Tabs please !
 
Join Date: Jun 2004
Location: Biffins Bridge
Posts: 710
I'm a bit of a whizz at this. Check that the autocalc is running. Formulas - Calculation Options - Automatic.

Both solutions suggested above will get the job done. "=sqrt(a1)" would be my preference.
B Fraser is offline  
Old 26th Mar 2020, 13:09
  #8 (permalink)  
 
Join Date: Oct 2002
Location: West Wiltshire, UK
Age: 67
Posts: 372
Originally Posted by rans6andrew View Post
Interestingly it is working for me here, this morning. Yesterday it failed with an error when selecting the E72 by mouse clicking on it. Even SWMBO, who plays spreadsheets more than I do failed to make it happen. There is a quirk somewhere in the system. Perhaps saving it and closing the machine down and restarting has cleared it..... That will bug me now, if it can't be relied upon.

What I am playing with is trying to measure true RMS AC power using a current transformer and voltage connection on our household mains. The readings need to be crunched in a PIC micro programmed in assembler and really need to work in real time. The PIC I am using has a hardware multiplier but no quick or easy way to calculate square roots. I think I can make the calculation of Vrms * Irms on multiple samples per cycle of the mains without actually doing any square root calculation. The end result is to enable me to switch stuff on dynamically whenever our solar panels produce enough excess power to run each item of stuff.

Thanks for trying.

Rans6

Off topic, but my solution to exactly the same requirement was to use a very cheap electricity meter chip. I used a (now obsolete) Analog Devices AD7755. This does all the heavy lifting, and includes a input that will accept a current transformer output, as well as an input that will accept an AC voltage reference from the power supply transformer winding. It outputs two logic level signals, one that indicates the direction of power flow (handy if you have a PV system) and the other that delivers a pulse train with a frequency proportional to true power (with PF correction applied). I interfaced this to a PIC, but the PIC is only doing trivial stuff to drive a display, control our immersion heater, etc, so doesn't need to do any serious mathematical operations.
VP959 is online now  
Old 26th Mar 2020, 13:47
  #9 (permalink)  
Thread Starter
 
Join Date: Sep 2004
Location: Berkshire, UK
Posts: 673
Hi VP959, we are thinking along the same lines except that I hate to see an under utilised PIC in a product. Adding parts costs money which doesn't really matter if you are only doing a one off but.........

Have you done anything to reduce the power requirement of your immersion heater? I don't often see 2.7kW of excess electricity going back to the mains but quite often see a goodly amount. Consequently I am going to reduce the power of the heater by connecting it to a 110 volt transformer and switching that automatically. It effectively reduces the power to about 600W so the system will be effective through more of the year. Having said that I saw 2.5kW of excess electricity on our system monitor a couple of hours ago.

Rans6........................
rans6andrew is offline  
Old 26th Mar 2020, 14:50
  #10 (permalink)  
 
Join Date: Oct 2002
Location: West Wiltshire, UK
Age: 67
Posts: 372
Originally Posted by rans6andrew View Post
Hi VP959, we are thinking along the same lines except that I hate to see an under utilised PIC in a product. Adding parts costs money which doesn't really matter if you are only doing a one off but.........

Have you done anything to reduce the power requirement of your immersion heater? I don't often see 2.7kW of excess electricity going back to the mains but quite often see a goodly amount. Consequently I am going to reduce the power of the heater by connecting it to a 110 volt transformer and switching that automatically. It effectively reduces the power to about 600W so the system will be effective through more of the year. Having said that I saw 2.5kW of excess electricity on our system monitor a couple of hours ago.

Rans6........................

What I've done is get the PIC to control a zero crossing solid state relay, so it pulses the heating element on and off. The PIC measures power for a period of time (so gets energy) and then turns on the heating element to deliver 0.5 J of energy. It fills a virtual "energy bucket" (that has a slow leak to prevent cumulative errors) with exported energy and when the "bucket" contains 0.75 J it "empties" it by turning on the heating element via the SSR. When the "bucket" empties via the heating element to 0.25 J the SSR is turned off and the "bucket" starts to fill again. This works because domestic meters have an anti-creep feature that means they usually have a 1 J threshold. You can shuttle less than 1 J across the meter OK without it recording anything. Very handy when it comes to using small amounts of PV generation.
VP959 is online now  
Old 26th Mar 2020, 16:25
  #11 (permalink)  
 
Join Date: Jun 2009
Location: Bedford, UK
Age: 66
Posts: 1,230
Originally Posted by rans6andrew View Post
Interestingly it is working for me here, this morning. Yesterday it failed with an error when selecting the E72 by mouse clicking on it. Even SWMBO, who plays spreadsheets more than I do failed to make it happen. There is a quirk somewhere in the system. Perhaps saving it and closing the machine down and restarting has cleared it..... That will bug me now, if it can't be relied upon.

What I am playing with is trying to measure true RMS AC power using a current transformer and voltage connection on our household mains. The readings need to be crunched in a PIC micro programmed in assembler and really need to work in real time. The PIC I am using has a hardware multiplier but no quick or easy way to calculate square roots. I think I can make the calculation of Vrms * Irms on multiple samples per cycle of the mains without actually doing any square root calculation. The end result is to enable me to switch stuff on dynamically whenever our solar panels produce enough excess power to run each item of stuff.

Thanks for trying.

Rans6
​​​​​​Do you mean V*I rather than Vrms etc?
Mr Optimistic is offline  
Old 27th Mar 2020, 10:04
  #12 (permalink)  
 
Join Date: Nov 1999
Location: UK
Posts: 1,295
I am intrigued. Can I check my understanding here. rans6andrew and VP959, are both using your PV output above a certain threshold to run various loads in your house instead of passing the whole output to the electricity company?


Am I right in guessing that is more efficient for you, because you get 100% of the excess instead of being paid an amount by the PV company which presumably is less than 100% for power supplied?


How do you decide the excess value?


Sounds like a good wheeze. I have been wondering how best to divert the PV inverter output into my house rather than sending it to the PV company, without cocking up their meter, or the supply company's meter.
Uplinker is offline  
Old 27th Mar 2020, 11:08
  #13 (permalink)  
 
Join Date: Oct 2002
Location: West Wiltshire, UK
Age: 67
Posts: 372
Originally Posted by Uplinker View Post
I am intrigued. Can I check my understanding here. rans6andrew and VP959, are both using your PV output above a certain threshold to run various loads in your house instead of passing the whole output to the electricity company?


Am I right in guessing that is more efficient for you, because you get 100% of the excess instead of being paid an amount by the PV company which presumably is less than 100% for power supplied?


How do you decide the excess value?


Sounds like a good wheeze. I have been wondering how best to divert the PV inverter output into my house rather than sending it to the PV company, without cocking up their meter, or the supply company's meter.

Roughly 75% of our hot water is "free" each year, in that it's heated by excess PV generation. The system works by measuring the power coming into, and out of the house, at the point where the power company cable comes in. It does this using a current transformer clipped around one of the meter tails.

The box of tricks just controls the power delivered to the heating element to try and keep the exported power to the grid as close to zero as possible when the PV system is generating, and to not allow the heater to draw power from the grid during the day, either. To get around the problem of cloudy days not generating enough to heat the water, I have a boost time switch set to come on in the night and make sure we have hot water for showers the following morning. We're on a time of use tariff (Economy 7) so overnight electricity is almost half the price of daytime electricity.

The box of tricks I built can effectively vary the power delivered to the heating element from a few tens of watts up to the 3 kW rating of the element, so even if we are only generating a bit more power than the house is using for other stuff, the excess can help to give us hot water.

There are several commercially available devices that can do this; they are pretty popular with those that have PV systems, as they can significantly reduce the cost of hot water, especially from around March to October, when a PV system tends to deliver most energy.
VP959 is online now  
Old 27th Mar 2020, 12:03
  #14 (permalink)  
 
Join Date: Nov 2015
Location: Farnham, Surrey
Posts: 1,234
Originally Posted by rans6andrew View Post
I think I can make the calculation of Vrms * Irms on multiple samples per cycle of the mains without actually doing any square root calculation.
I guess I'm being thick, but unless your voltage and current sensors are using very short samples (around 1msec) and are synchronised so that those sampling points were within a tenth of that of eachother, you won't get an accurate reading anyway due to phase errors, surely?

PDR
PDR1 is offline  
Old 27th Mar 2020, 12:24
  #15 (permalink)  
 
Join Date: Jun 2009
Location: Bedford, UK
Age: 66
Posts: 1,230
Couldn't quite figure out the need for square rooting either unless things are being squared first. For the accuracy being considered, couldn't the load be treated as a constant resistance so measure it and then calculate using voltage only?
Mr Optimistic is offline  
Old 27th Mar 2020, 12:28
  #16 (permalink)  
 
Join Date: Oct 2002
Location: West Wiltshire, UK
Age: 67
Posts: 372
Originally Posted by PDR1 View Post
I guess I'm being thick, but unless your voltage and current sensors are using very short samples (around 1msec) and are synchronised so that those sampling points were within a tenth of that of eachother, you won't get an accurate reading anyway due to phase errors, surely?

PDR

True. The metering chip I'm using in my box of tricks has two (one for current, one for voltage) 16 bit second order sigma delta ADCs, over-sampling at 900 kHz, so that it can get a high degree of accuracy over the wide dynamic range needed.
VP959 is online now  
Old 27th Mar 2020, 12:37
  #17 (permalink)  
 
Join Date: Jun 2009
Location: Bedford, UK
Age: 66
Posts: 1,230
Yep, that is oversampling to a heroic degree 🙂
Mr Optimistic is offline  
Old 27th Mar 2020, 22:43
  #18 (permalink)  
Thread Starter
 
Join Date: Sep 2004
Location: Berkshire, UK
Posts: 673
I am looking to do 32 samples per cycle of the mains, which in the uk runs at 50Hz, so the samples are every 625uS. Sampling V and I using synchronised sample and hold circuits may be needed to be sure that the phase error is minimised. This sample rate is easily is doable on 2 channels (V and I) of a PIC microcontroller. The RMS calculation may be a bit of overkill but it does feel to be the right way of doing things given that I don't know the power factor of all of the domestic loads. I suspect that things with switch mode power supplies in have some quite horrible current waveforms. It will be interesting to see what the solar panels do to the current wafeform when they inject up to 16A back into my house circuit. I also don't know how smart the digital metering systems which are installed in my house are. I bet you would have been able to steal odd cycles of electricity with the old style rotating aluminium disc meter but doubt that you can with current installed equipment.

Uplinker. The way the solar panel tariff works is that the electricity coming into the house is metered in the normal way, the meter provided by the normal electricity supply company is not changed when the solar panels are installed. A second meter is installed which measures the power entering the house fuse box from the solar panel system. The normal electricity meter totals up the power coming into the house when there is a net consumption but doesn't do anything when the house is putting electricity back into the grid, it just holds the running total. The second meter only totals up the power generated by the solar system. So your normal electricity is billed according to the normal meter by your current supplier. The solar panel generated amount shown on the second meter is paid to you by your FIT (feed in tariff) administrator, which may or may not be your normal supplier. The quirk in the whole business is that the normal supply costs me 13.8 pence per kilowatt hour. This is unavoidable. The Solar FIT pays a healthy sum for the power being generated, currently about 50 pence per kilowatt hour, plus a much smaller sum, about 3 pence per kilowatt hour for the amount of power ASSUMED to be supplied back to the grid. The ASSUMED pay back to the grid is 50% of the solar power being generated, this bit is not metered so, if you don't actively try to use it the grid does get it fed back in. If you do find a way of using it for your own benefit you don't lose anything.

Mr Optimisti.The only true way of measuring V or I of an AC waveform is to do and RMS measurement. This stands for Root Mean Square readings where you take, ideally an infinite number of samples of a cycle of the waveform, square each one, add the squares together and divide by the number of samples to get the mean and then take the square root of the mean. It can be approximated by dividing the peak voltage by the square root of 2 IF the waveform is a sine wave. You do this RMS calculation for both Volts and Amps at the same sample times and then multiply the two RMS values together to get the true power. This can be done for any shape of waveform.

Rans6........
rans6andrew is offline  
Old 27th Mar 2020, 23:53
  #19 (permalink)  
 
Join Date: Nov 2004
Location: UK
Posts: 505
Originally Posted by VP959 View Post
What I've done is get the PIC to control a zero crossing solid state relay, so it pulses the heating element on and off. The PIC measures power for a period of time (so gets energy) and then turns on the heating element to deliver 0.5 J of energy.
You do realise that 0.5 J of energy is 0.5 W.s, don't you? In other words a 1/60th of the energy of a single half cycle of 50Hz mains driven 3kW load, a burst of well under 1ms. I think you've muddled up your units, or certainly the magnitude of them.

Originally Posted by VP959 View Post
When the "bucket" empties via the heating element to 0.25 J the SSR is turned off and the "bucket" starts to fill again.
How do you turn off your SSR mid-cycle? Good luck with that!

Originally Posted by VP959 View Post
This works because domestic meters have an anti-creep feature that means they usually have a 1 J threshold. You can shuttle less than 1 J across the meter OK without it recording anything. Very handy when it comes to using small amounts of PV generation.
Not true. Older 'analogue' meters have a threshold of around 2kJ, meaning a typical 3kW load could be pulsed on and off at aprox 1Hz (50% duty) without being registered with a 1.5kW excess of base exported energy. Smart meters, by measuring up to around 8kHz cannot be 'fudged' in this way, meaning any power controller needs to produce effectively an analogue sinewave voltage, through rapid switching PWM control or other such. Your apparent burst switching method would not work with any smart meter, and in any case is illegal.

Pulsing a 3kW load rapidly (in the way I believe you are doing) massively exceeds the Harmonics and Flicker Regulations for loads applied to the mains supply, and is illegal. It also puts undue strain on the inverter, which can cause premature failure, the cost of replacement would far outweigh any saving of water heating costs. It is also very annoying for other households nearby on the same phase as you - which is precisely why it is illegal to do so. It is also the reason why I never marketed my first switching power solar-controller design, which used similar zero voltage burst switching control, and instead went on to develop a better design which uses PWM control to do the job properly - and legally, without p1$$1ng off the neighbours.
pilotmike is offline  
Old 28th Mar 2020, 02:02
  #20 (permalink)  
 
Join Date: Oct 2004
Location: California
Posts: 278
Originally Posted by rans6andrew View Post
The normal electricity meter totals up the power coming into the house when there is a net consumption but doesn't do anything when the house is putting electricity back into the grid, it just holds the running total.
It would be simpler to adopt the US system of Net Energy Metering, where the meter runs backwards when you are putting out more solar than you are using.
MarcK is offline  

Thread Tools
Search this Thread

Contact Us - Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.