anyone good with excel? help...

[Polak]

I'm going to describe my problem the best I can:
I need to convert an amplitude vs time chart (data dumped straight from an oscilloscope):
to amplitude vs degrees chart.

I'm able to do this manually for one time but I need to write a code for ATE (automated testing). I need to come up with a code for the "Continuous phase" column.

Here are how the cells aligned: I'm converting this points using X = Xo sin(wt)

xo =.04 (calculated constant); W (omega) rad/sec
(Time) ||||||||| X ||||||||| Sin^-1(X/Xo) |||| Wrad |||||||| Wdeg |||||||| Continuous phase
8.2E-09 |||||||| 0.03 |||||||| 1.015 ||||||||| 1.48 |||||||| 84.79 ||||||||
8.4E-09 |||||||| 0.034 |||||||| 0.994 |||||||| 1.46 |||||||| 83.87 ||||||||
8.6E-09 |||||||| 0.035 |||||||| 0.999 |||||||| 1.56 |||||||| 89.74 ||||||||
8.8E-09 |||||||| 0.034 |||||||| 0.996 |||||||| 1.48 |||||||| 84.97 |||||||| 95.022 <<"=180-E51
9.0E-09 |||||||| 0.033 |||||||| 0.946 |||||||| 1.24 |||||||| 71.09 |||||||| 108.902
9.2E-09 |||||||| 0.033 |||||||| 0.948 |||||||| 1.24 |||||||| 71.46 |||||||| 108.535
9.4E-09 |||||||| 0.033 |||||||| 0.947 |||||||| 1.24 |||||||| 71.33 |||||||| 108.664

As you see with the sin, the points start to inverse (reverse slope), and eventually i need to do +360 - E(whatever). ex:
.
.
.
Time |||||||| X |||||||| Sin |||||||| Wrad |||||||| Wdeg
2.30E-08 |||||||| -0.0354 |||||||| -0.98 |||||||| -1.3704 |||||||| -78.52 |||||||| 258.521659
2.32E-08 |||||||| -0.0359 |||||||| -0.99 |||||||| -1.4292 |||||||| -81.89 |||||||| 261.8903855
2.34E-08 |||||||| -0.0347 |||||||| -0.99 |||||||| -1.4707 |||||||| -84.26 |||||||| 264.268032
2.36E-08 |||||||| -0.0355 |||||||| -1 ||||||||||| -1.5707 |||||||| -90 ||||||||||| 270 <<<<< =360 + (-90)
2.38E-08 |||||||| -0.0350 |||||||| -0.99 |||||||| -1.4707 |||||||| -84.26 |||||||| 275.731968
2.40E-08 |||||||| -0.0336 |||||||| -0.96 |||||||| -1.2876 |||||||| -73.77 |||||||| 286.223624


Can someone come up with an IF/And Formula that subtracts 180, then adds 360 then subtracts 540 etc etc every time the slop changes (as in the Wdeg changes from going up to down or vice versa)????

I'm not terrible with excel but I can't get my finger around this
Thanks Rob

[BrentMusburger]

Well, I'm certified in excel...

Say you take the WDeg cell with 89.74 (it's hard without using the cell references).

If *click next cell down" is less than 89.74 (meaning the next box went from a positive direction back to declining again), then -180. Then the next formula would have to be If "cell we just created with last formula" is greater than *new number*. Then +360.

I'm not exactly sure what you mean because I don't understand the (I'm assuming) engineering that you're doing. However, setting up the formula itself to do what you want isn't that hard using IF/Then.

But, I don't know of a way to create one formula and just have you copy/paste it all the way down if you have pages and pages of data, without having to manually edit the formula at each change like I outlined above....

Which is probably the reason why your boss gave this to you~

Have fun with that!

[Swiss]

I'm pretty sure I came up with an if statement that solves this problem. I have one question though.

My equation slips up at the last two values that you have. If the third line in the negative portion is (180-(-84.26)=264.268032), shouldn't the other line with -84.26 two rows down equal the same value (264.269032)?

If so, then I am fairly confident my equation will work for you

Edit: oh and my values will be slightly off since I think you gave a rounded version of the wdeg value. let me know

Edit 2: and also, shouldnt the last line be 253.77 (180-(-73.77))

[motive]

i dont get it

here
fuck this signal processing shit or whatever the fuck

Code: Select all

<?php
	function csv_to_array($input, $delimiter=',') 
	{ 
		$header = null; 
		$data = array(); 
		$csvData = str_getcsv($input, "\n"); 
		
		foreach($csvData as $csvLine){ 
			if(is_null($header)) $header = explode($delimiter, $csvLine); 
			else{ 
				
				$items = explode($delimiter, $csvLine); 
				
				for($n = 0, $m = count($header); $n < $m; $n++){ 
					$prepareData[$header[$n]] = $items[$n]; 
				} 
				
				$data[] = $prepareData; 
			} 
		} 
		
		return $data; 
	} 


	$dt = csv_to_array(file_get_contents("C:\\Book1.csv"));
	$m = null; // slope +/-
	$O_d_0 = null; // omega_t-1
	$op = 0; // 1=>add, 0=>sub
	$mult = 1;

	foreach($dt as $dr) {
		if(is_null($m) && !is_null($O_d_0)) {
			$m = ($dr["O_d"] > $O_d_0 ? 1 : 0);
		} elseif(is_null($m) && is_null($O_d_0)) {
			// fuck you
		} elseif($m == 1 && $dr["O_d"] < $O_d_0)	{
			$op = ($op == 1 ? 0 : 1);
			$mult++;
			$m = 0;
		} elseif($m == 0 && $dr["O_d"] > $O_d_0) {
			$op = ($op == 1 ? 0 : 1);
			$mult++;
			$m = 1;
		}
		
		
		if($op == 1) {
			$dr["phi"] = ($mult * 180) + $dr["O_d"];
			print "m=${m}: " . ($mult * 180) . " + " . $dr["O_d"] . " = " . $dr["phi"];
		} else {
			$dr["phi"] = ($mult * 180) - $dr["O_d"];
			print "m=${m}: " . ($mult * 180) . " - " . $dr["O_d"] . " = " . $dr["phi"];
		}
		
		print "\n";
		foreach($dr as $dr_v) print "${dr_v},";
		
		print "\n\n";
		
		$O_d_0 = $dr["O_d"];
	}
?>

Code: Select all

m=: 180 - 84.79 = 95.21
8.2E-09,0.03,1.015,1.48,84.79,95.21,

m=0: 180 - 83.87 = 96.13
8.4E-09,0.034,0.994,1.46,83.87,96.13,

m=1: 360 + 89.74 = 449.74
8.6E-09,0.035,0.999,1.56,89.74,449.74,

m=0: 540 - 84.97 = 455.03
8.8E-09,0.034,0.996,1.48,84.97,455.03,

m=0: 540 - 71.09 = 468.91
9.0E-09,0.033,0.946,1.24,71.09,468.91,

m=1: 720 + 71.46 = 791.46
9.2E-09,0.033,0.948,1.24,71.46,791.46,

m=0: 900 - 71.33 = 828.67
9.4E-09,0.033,0.947,1.24,71.33,828.67,

m=0: 900 - -78.52 = 978.52
2.30E-08,-0.0354,-0.98,-1.3704,-78.52,978.52,

m=0: 900 - -81.89 = 981.89
2.32E-08,-0.0359,-0.99,-1.4292,-81.89,981.89,

m=0: 900 - -84.26 = 984.26
2.34E-08,-0.0347,-0.99,-1.4707,-84.26,984.26,

m=0: 900 - -90 = 990
2.36E-08,-0.0355,-1,-1.5707,-90,990,

m=1: 1080 + -84.26 = 995.74
2.38E-08,-0.0350,-0.99,-1.4707,-84.26,995.74,

m=1: 1080 + -73.77 = 1006.23
2.40E-08,-0.0336,-0.96,-1.2876,-73.77,1006.23,

[Sears]

nerds

[Swiss]

jealous.

[Sears]

jealous.

[BrentMusburger]

nerds

It sounds like the teacher is jelly that his students are smarter than him~

[Sears]

OMG IM JELLY SO HARD...

[BrentMusburger]

OMG IM JELLY SO HARD...

[Polak]

Thanks for the feed back guys, I ended up over complicating the whole thing.

All I had to do was multiply my time domain by 2PI() (Frequency).

I instead tried to calculate a bunch of constants instead of using W = 2(PI) F whatever.

and I'm doing microwave engineering work. its very depressing at times

[cassinoroyale]

whoah... financial excel formulas usage aren't anything close to this!

searsizmad

[cassinoroyale]

whoah sears neg power really hurts!

[mg_]

whoah sears neg power really hurts!

[youtube]JZSy2JH8iu4#t=5s[/youtube]

[milo]

I still can't rep anyone

[mg_]

You have to be at 100 I think.

[Sears]

I still can't rep anyone

Yeah you are on the spammers list. As in it won't let you have rep abilities ever.

[mg_]

I still can't rep anyone

Yeah you are on the spammers list. As in it won't let you have rep abilities ever.

[milo]

[k4b00m]

[milo]

[Polak]

What happened to this forum and um

wtf does rep mean and neg