anyone good with excel? help...

Post a reply

Confirmation code
Enter the code exactly as it appears. All letters are case insensitive.
Smilies
:D :) ;) :( :o :shock: :? 8-) :lol: :x :P :oops: :cry: :evil: :twisted: :roll: :!: :?: :idea: :arrow: :| :mrgreen: :geek: :ugeek:

BBCode is ON
[img] is ON
[url] is ON
Smilies are ON

Topic review
   

Expand view Topic review: anyone good with excel? help...

Re: anyone good with excel? help...

by Polak » Wed Dec 07, 2011 9:13 am

What happened to this forum and um

wtf does rep mean and neg

Re: anyone good with excel? help...

by milo » Wed Dec 07, 2011 6:28 am

Image

Re: anyone good with excel? help...

by k4b00m » Tue Dec 06, 2011 11:10 pm

Image

Re: anyone good with excel? help...

by milo » Tue Dec 06, 2011 11:08 am

Image

Re: anyone good with excel? help...

by mg_ » Tue Dec 06, 2011 11:00 am

Sears wrote:
milo wrote:I still can't rep anyone :|
Yeah you are on the spammers list. As in it won't let you have rep abilities ever.
Image

Re: anyone good with excel? help...

by Sears » Tue Dec 06, 2011 10:51 am

milo wrote:I still can't rep anyone :|
Yeah you are on the spammers list. As in it won't let you have rep abilities ever.

Re: anyone good with excel? help...

by mg_ » Tue Dec 06, 2011 10:40 am

You have to be at 100 I think.

Re: anyone good with excel? help...

by milo » Tue Dec 06, 2011 9:09 am

I still can't rep anyone :|

Image

Re: anyone good with excel? help...

by mg_ » Tue Dec 06, 2011 9:07 am

cassinoroyale wrote:whoah sears neg power really hurts! :cry:
[youtube]JZSy2JH8iu4#t=5s[/youtube]

Re: anyone good with excel? help...

by cassinoroyale » Tue Dec 06, 2011 6:03 am

whoah sears neg power really hurts! :cry:

Re: anyone good with excel? help...

by cassinoroyale » Mon Dec 05, 2011 7:31 pm

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

searsizmad

Re: anyone good with excel? help...

by Polak » Mon Dec 05, 2011 10:17 am

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

Re: anyone good with excel? help...

by BrentMusburger » Sat Dec 03, 2011 7:50 am

Sears wrote:OMG IM JELLY SO HARD...
Image

Re: anyone good with excel? help...

by Sears » Sat Dec 03, 2011 7:24 am

OMG IM JELLY SO HARD...

Re: anyone good with excel? help...

by BrentMusburger » Sat Dec 03, 2011 4:54 am

Sears wrote:nerds
It sounds like the teacher is jelly that his students are smarter than him~

Re: anyone good with excel? help...

by Sears » Sat Dec 03, 2011 12:10 am

Swiss wrote:jealous.
Image

Re: anyone good with excel? help...

by Swiss » Sat Dec 03, 2011 12:00 am

jealous.

Re: anyone good with excel? help...

by Sears » Fri Dec 02, 2011 11:56 pm

nerds

Re: anyone good with excel? help...

by motive » Fri Dec 02, 2011 11:27 pm

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,

Re: anyone good with excel? help...

by Swiss » Fri Dec 02, 2011 11:26 pm

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))

Re: anyone good with excel? help...

by BrentMusburger » Fri Dec 02, 2011 3:04 pm

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!

anyone good with excel? help...

by Polak » Fri Dec 02, 2011 2:04 pm

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

Top