Call Search
     

New to Ham Radio?
My Profile

Community
Articles
Forums
News
Reviews
Friends Remembered
Strays
Survey Question

Operating
Contesting
DX Cluster Spots
Propagation

Resources
Calendar
Classifieds
Ham Exams
Ham Links
List Archives
News Articles
Product Reviews
QSL Managers

Site Info
eHam Help (FAQ)
Support the site
The eHam Team
Advertising Info
Vision Statement
About eHam.net

   Home   Help Search  
Pages: [1]   Go Down
  Print  
Author Topic: Excel data  (Read 3697 times)
W8JI
Member

Posts: 9304


WWW

Ignore
« on: October 05, 2012, 02:18:03 PM »

I'm excel dumb. I have a spreadsheet that I dump data from my VNA into. The data is S parameter data, and I convert it to  R j  and Z with standard formulas.

For example this formula converts 50 ohm normalized S11 Real or Imaginary  into R and j:

=50*((1+cell data)/(1-cell data))

The problem I have is normal measurement noise results in the S parameter being 1 or more. I get places where this happens. Here is an example:

1.00E+00   4.83E-02   #DIV/0!   55.07281020   #DIV/0!

This is because the S parameter measurement cell data at 1.00E+00 is really 1.00000, instead of 0.99999 or some more reasonable number. This error is just from normal measurement noise.

What I want is an IF statement that if cell data (let's say cell B67) is 1.00000 or more, then it should be considered B67=0.99999  

The problem is I can't figure out how to do an IF statement mixed in with the formula. In other words I want to change this formula:

=50*((1+B67)/(1-B67))

to something where if B67 > 0.99999 then B67= 0.99999

I'd like to do this without altering the data in B67, just by altering the formula.

I could also set the lower number (1-B67) to never go below .0000001

Can anyone help with this?? There are thousands of cells in one measurement sweep.

Thanks, Tom  

 

 
« Last Edit: October 05, 2012, 02:20:56 PM by W8JI » Logged
K0BT
Member

Posts: 176




Ignore
« Reply #1 on: October 05, 2012, 03:13:35 PM »


Could you try this and see if it works?

=IF(B67<0.0000001,0.0000001,IF(B67>0.99999,0.99999,50*((1+B67)/(1-B67))))

Bob, K0BT
Logged
W8JI
Member

Posts: 9304


WWW

Ignore
« Reply #2 on: October 05, 2012, 06:57:41 PM »

Thanks Bob , but no. Doesn't work. I'll play around with it some more tomorrow.
Logged
N5NA
Member

Posts: 217




Ignore
« Reply #3 on: October 05, 2012, 07:12:40 PM »

How about:

=50*((1+(IF(B67>0.9999,0.9999,B67)))/(1-(IF(B67>0.9999,0.9999,B67))))

Alan N5NA
Logged
K3AN
Member

Posts: 787




Ignore
« Reply #4 on: October 05, 2012, 07:15:15 PM »

Is the value you're concerned with a number that is inputted, or is it the result of a calculation? If it's a number you inputted, you cannot use an if/then construct to change its value. What you need to do is leave that value as it's inputted, and then write an if/then statement for some other cell that is either the value of the input cell or 0.999, depending on the input cell's value.

Say B3 contains the inputted value, which may equal or exceed 1.0. Write a formula for the adjacent cell C3 such as

@IF(B3<1,B3,0.999)

If the first part of the statement is true then C3 will contain the value of B3, otherwise C3 will contain the value 0.999. Then use the cell C3 to carry out your further calculations.
Logged
W9IQ
Member

Posts: 102




Ignore
« Reply #5 on: October 07, 2012, 10:18:53 PM »

Hi Tom,

Using your example of cell B67 containing the input variable to the equation, I believe you will find a simple version of the IF statement to be as follows:

=IF(B67<1,50*((1+B67)/(1-B67)),9999999)

The formula says that if B67 is <1 then use your formula otherwise return 9999999. There is no need to continually recompute the resultant if B67>=1.

You can of course change the number to the right of the comma to be any result you wish in the event B67>=1.

Glenn DJ0IQ and W9IQ
Logged
W8JI
Member

Posts: 9304


WWW

Ignore
« Reply #6 on: October 08, 2012, 12:49:05 AM »

Hi Tom,

Using your example of cell B67 containing the input variable to the equation, I believe you will find a simple version of the IF statement to be as follows:

=IF(B67<1,50*((1+B67)/(1-B67)),9999999)

The formula says that if B67 is <1 then use your formula otherwise return 9999999. There is no need to continually recompute the resultant if B67>=1.

You can of course change the number to the right of the comma to be any result you wish in the event B67>=1.

Glenn DJ0IQ and W9IQ

Thank you Glen, that one did it. I just can't understand MS program help all the time. Although my formula was initially wrong, I have corrected it and can use the "IF" statement. :-)
Logged
W9IQ
Member

Posts: 102




Ignore
« Reply #7 on: October 08, 2012, 01:47:56 AM »

Hi Tom,

I am glad it worked for you. I agree with your assessment of the Excel help  - it often provides cloudy clarification!

- Glenn Dj0IQ and W9IQ
Logged
W8JI
Member

Posts: 9304


WWW

Ignore
« Reply #8 on: October 09, 2012, 04:58:51 PM »

I had to make some changes to what you wrote, but I have it now. Thanks!
Logged
Pages: [1]   Go Up
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.11 | SMF © 2006-2009, Simple Machines LLC Valid XHTML 1.0! Valid CSS!