noise curve in excel

[spanikakamuni]

Hello,
I am very new to excel.
I want to create a noise curve with a crest factor of 5 using excel. My frequency(1/T) range is 100khertz to 220Mhertz.
i normally used Norm.inv(RAND(),0,1) to make some random voltages for my curve.
but now i am asked to consider a crest factor of 5 for each voltage point i make on my curve. I am really confused. how can i make it work.
thanking you,
spanika kamuni

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

The Crest Factor is the maximum peak height divided by the RMS value. The way you are generating your samples at present seems to give varying crest factors of between 3.7 and 4.6. I based that on about 20,000 samples. The variation will be greater if you use fewer samples. So it is possible that some of your samples may already have a crest factor of 5.

Changing the crest factor is easy, you can start from the signal you already have and raise it to a power of some number. That is only one method and may not be what is being sought.

The main problem is how you limit the frequency range. Do you perform any filtering on the data at present? If not then the signal may well have components outside your requested range. I don't know if that is an issue for you.

Here is the start of the data I looked at. Obviously, I have omitted most of the rows but it will show you what I did.
I used the MAX function to find the peak and the STDDEV function to find the RMS value. Then divided one by the other to get the crest factor. That is displayed in row 3. I added a sine wave to make sure the figures were working out as I expected. (Sine waves have crest factors of 1.414 i.e. SQRT(2).)

The data in column B is your original formula and the data in column C is that data which has been made more "peaky".

Excel 2013
	A	B	C	D
1	1.2	3.874013176	5.07917659	1.41421356
2		0.997939726	1.07815294	0.99955143
3		3.882011183	4.71099823	1.41484822
4	Column1	Original	New	Sine
5	0	2.266698628	2.66976194	0
6	1	-0.325267623	-0.2598298	0.14118577
7	2	-0.209587886	-0.1533343	0.28096086
8	3	0.319815629	0.25461244	0.41792868
9	4	-0.361070562	-0.2945171	0.5507207
10	5	0.300948623	0.23669595	0.6780101
11	6	-1.839998355	-2.0786479	0.79852504
12	7	-0.643265587	-0.5889364	0.91106139
13	8	0.861779711	0.83651844	1.01449471
14	9	0.237440194	0.17810048	1.10779154
15	10	0.507357314	0.44297244	1.19001968
16	11	1.110598265	1.13414449	1.26035754
17	12	1.109681813	1.13302153	1.31810232
Sheet1

Cell Formulas
Range	Formula
B1	=MAX(B5:B20004)
B2	=STDEV.P(B5:B20004)
B3	=B1/B2
B5	= NORM.INV(RAND(),0,1)
B6	= NORM.INV(RAND(),0,1)
B7	= NORM.INV(RAND(),0,1)
B8	= NORM.INV(RAND(),0,1)
B9	= NORM.INV(RAND(),0,1)
B10	= NORM.INV(RAND(),0,1)
B11	= NORM.INV(RAND(),0,1)
B12	= NORM.INV(RAND(),0,1)
B13	= NORM.INV(RAND(),0,1)
B14	= NORM.INV(RAND(),0,1)
B15	= NORM.INV(RAND(),0,1)
B16	= NORM.INV(RAND(),0,1)
B17	= NORM.INV(RAND(),0,1)
C1	=MAX(C5:C20004)
C2	=STDEV.P(C5:C5004)
C3	=C1/C2
C5	=SIGN(B5)*(ABS(B5)^$A$1)
C6	=SIGN(B6)*(ABS(B6)^$A$1)
C7	=SIGN(B7)*(ABS(B7)^$A$1)
C8	=SIGN(B8)*(ABS(B8)^$A$1)
C9	=SIGN(B9)*(ABS(B9)^$A$1)
C10	=SIGN(B10)*(ABS(B10)^$A$1)
C11	=SIGN(B11)*(ABS(B11)^$A$1)
C12	=SIGN(B12)*(ABS(B12)^$A$1)
C13	=SIGN(B13)*(ABS(B13)^$A$1)
C14	=SIGN(B14)*(ABS(B14)^$A$1)
C15	=SIGN(B15)*(ABS(B15)^$A$1)
C16	=SIGN(B16)*(ABS(B16)^$A$1)
C17	=SIGN(B17)*(ABS(B17)^$A$1)
D1	=MAX(D5:D20004)
D2	=STDEV.P(D5:D5004)
D3	=D1/D2
D5	=SQRT(2)*SIN(A5/10)
D6	=SQRT(2)*SIN(A6/10)
D7	=SQRT(2)*SIN(A7/10)
D8	=SQRT(2)*SIN(A8/10)
D9	=SQRT(2)*SIN(A9/10)
D10	=SQRT(2)*SIN(A10/10)
D11	=SQRT(2)*SIN(A11/10)
D12	=SQRT(2)*SIN(A12/10)
D13	=SQRT(2)*SIN(A13/10)
D14	=SQRT(2)*SIN(A14/10)
D15	=SQRT(2)*SIN(A15/10)
D16	=SQRT(2)*SIN(A16/10)
D17	=SQRT(2)*SIN(A17/10)

 

[RickXL]

Oh, I forgot ...


The value in A1 is an arbitrary factor.

If you set it to 1 then you will get a copy of your usual data in column C.
If you make A1 bigger it will increase the Crest Factor.

Because you are using a Normal Distribution you need a lot of samples to make the numbers repeatable.

 

[spanikakamuni]

Oh, I forgot ...
The value in A1 is an arbitrary factor.

If you set it to 1 then you will get a copy of your usual data in column C.
If you make A1 bigger it will increase the Crest Factor.

Because you are using a Normal Distribution you need a lot of samples to make the numbers repeatable.

Thank you very much. your Information helped me a lot.
for now I put the frequency aside. i made 65535 (16 bit)samples.
I understand that crest factor is Peak/rms value in electronic Terms. but in Excel how are we doing it. i really dont understand. can you please explain it a bit more.
I didnot understand the formula used in 'new column'. can you expand a Little more on it.

I have one more Question.
My Supervisor asks me to make the x axis to have 65535 samples.
make y axis with voltages of (16 bit) and having crest factor 5 without using rand function and then jumble the numbers. I really dont understand what he meant by this.

 

[spanikakamuni]

I have one more doubt. I should have a dc Offset as Zero when i make a crest factor of 5 since i am feeding this Signal into a transformer. how is this posible. I mean i dont understand how this Statement is taken into consideration when making a curve in Excel.

 

[RickXL]

I understand that crest factor is Peak/rms value in electronic Terms. but in Excel how are we doing it. i really dont understand. can you please explain it a bit more.

If you want an over view of "peakiness" you might take a look here: https://en.wikipedia.org/wiki/Kurtosis
Kurtosis is how a statistician measures it.

From a time series point of view, the peak value is obviously the maximum value and the rms value is related to the area under the curve. So to make a signal more "peaky" you need to increase its maximum value and reduce the area under the curve.

If you imagine starting with a sine wave, you could pinch each half cycle to make it narrower but have a higher peak. That would increase its crest factor and its Kurtosis. So I just used the first idea that popped into my head that would do that to your data.

I didnot understand the formula used in 'new column'. can you expand a Little more on it.

If you have a positive number less than 1, if you square it you will get an even smaller number.
On the other hand, if you have a number larger than 1 then when you square it the number will become bigger.
So, if you have positive numbers some less than 1 and some more than 1 if you square them all you will stretch the numbers out. This is like "pinching" the sine wave as suggested above.

If you raise the numbers to the power of one instead there will be no change. A power less than one will make the waveform "fatter" while numbers above one will make them thinner or peakier.

Try plotting out the data below and then changing the value in call A1. You will see how the Y2 curve varies.

Excel 2013
	A	B	C	D
1	2	Max:	1.1	1.21
2		StdDev:	0.345205	0.394099
3		Average:	0.55	0.421667
4		RMS:	0.895205	0.815765
5		Crest Fac:	1.228768	1.483269
6		Kurtosis:	-1.21569	-0.75921
7		X	Y1	Y2
8		0	0	0
9		1	0.1	0.01
10		2	0.2	0.04
11		3	0.3	0.09
12		4	0.4	0.16
13		5	0.5	0.25
14		6	0.6	0.36
15		7	0.7	0.49
16		8	0.8	0.64
17		9	0.9	0.81
18		10	1	1
19		11	1.1	1.21
20		12	0	0
21		13	0.1	0.01
22		14	0.2	0.04
23		15	0.3	0.09
24		16	0.4	0.16
25		17	0.5	0.25
26		18	0.6	0.36
27		19	0.7	0.49
28		20	0.8	0.64
29		21	0.9	0.81
30		22	1	1
31		23	1.1	1.21
Sheet2

Cell Formulas
Range	Formula
C1	=MAX(Sheet2!$C$8:$C$31)
C2	=STDEV.P(Sheet2!$C$8:$C$31)
C3	=AVERAGE(Sheet2!$C$8:$C$31)
C4	=C3+C2
C5	=C1/C4
C6	=KURT(Sheet2!$C$8:$C$31)
D1	=MAX(Sheet2!$D$8:$D$31)
D2	=STDEV.P(Sheet2!$D$8:$D$31)
D3	=AVERAGE(Sheet2!$D$8:$D$31)
D4	=D3+D2
D5	=D1/D4
D6	=KURT(Sheet2!$D$8:$D$31)
D8	=Sheet2!$C8^$A$1

 

[RickXL]

I have one more doubt. I should have a dc Offset as Zero when i make a crest factor of 5 since i am feeding this Signal into a transformer. how is this posible. I mean i dont understand how this Statement is taken into consideration when making a curve in Excel.

If you need a zero dc offset then you need to make sure that the average value is zero.

I assumed it was in my original answer but in the one above I have added in the average value to the calculation and included that in the rms value calculation. That was mainly because I was going to use a signal with a positive dc offset in my example.

 

[RickXL]

There is another issue when thinking about "zero offsets" and my original formula. Raising negative numbers to powers is not quite so easy as doing it with positive ones. So I made sure that I only did it with positive ones.

=B5^1.2
will work if B5 is positive but will fail if B5 is negative. So I split the sign away from the number, applied the power to the positive (ABS) part then re-applied the sign afterwards.

=SIGN(B5)*(ABS(B5)^$A$1)

I hope that makes sense.

=================================

Another problem is what to do about ensuring a 16-bit signal.

16 bits means that the signal can have no more than 256 separate levels because 2^16 = 256.
Be careful because there is a gotcha here if you need zero dc offset. If you use 1 to 128 for the positive numbers and -1 to 127 for negative numbers that introduces a slight bias in the positive direction. How you process the data depends on what you want to do about zero. Probably the easiest thing is to keep zero as zero then use only -127 to +127 and forget about +128.

=================================

If you need to create 65536 numbers in a random order then create the numbers, add a column with random numbers in it then sort the whole lot by the random number.

=================================

Also remember that if you are using signals with no dc offset then they will swing plus and minus so when you work out the peak value you will need to take into account the negative peaks. One way to do that is:
=MAX(MAX(A1:A65536),ABS(MIN(A1:A65536)))
but there are lots of ways to do that.

=================================

Regards,

 

[spanikakamuni]

thank you, for your guidance

 

[RickXL]

No problem - I hope it was useful.

Regards,