Excel gurus

NetNathan

Lifetime Supporting Member
Join Date
Nov 2011
Location
Corona, CA
Posts
2,191
I have a dewpoint sensor the is 4-20mA out across -148 to 68 Deg F.
I want to find out what the dewpoint reading should be for each mA.

I have a column of cells that are showing 4-20mA in 1mA steps.
The column next to it starts at -148 and last cell is 68. How do I tell Excel to evenly divide the range across the middle cells to cover 5-19mA range?
 
Just a linear slope.

Slope=((68-(-148))/(20-4)) = 13.5
Offset=68 -(13.5*20) = -202

Temp=(mA * 13.5) - 202
 
[Update: too late!]

Seriously?

Assuming 4 is in A2, 20 is in A18, -148 is in B2, and 68 is in B18, put either this
=B$2+((B$18-B$2)*(A3-A$2)/(A$18-A$2))



or this
=B2+13.5


into B3, then copy that to B4 through B18.

Or just put this
=(A2*13.5)-202


in B2 (or immediately to the right of wherever 4mA is, changing A2 in that formula to be the cell that contains 4mA), and copy that to the next 16 cells below it.

 
Put your -148 against 4mA
Put your 68 against 20mA
and then select the -148 cell to the 68 cell and select Fill then Linear fill and all the cells in between will populate with values.
 
How do I tell Excel to evenly divide the range across the middle cells to cover 5-19mA range?

The equation part of this question has been solved by other posters. As for "Evenly" do you mean in really small real increments and not integrals?

To do that, start by just filling A1= 4, A2= 4.01, A3 = 4.02 and then drag it downwards till you get to your value of 20.

Then use the linear equations from earlier to fill in the dew points in another column using A.
 
Put your -148 against 4mA
Put your 68 against 20mA
and then select the -148 cell to the 68 cell and select Fill then Linear fill and all the cells in between will populate with values.

This is the winner!
I knew Excel had a simple way of doing this.
This is one to remember for troubleshooting a signal to the PLC for conversion comparison.
Thanks..

And thanks to all for the other choices.
 
Last edited:

Similar Topics

Hi, I'm trying to export data from a DataGrid to Excel using VBA, but I'm getting an error "Object doesn't support this property or method". The...
Replies
0
Views
63
I don't know if this is the right place for the subject at least I'll try. The company has one of the oldest computers that is a master Windows...
Replies
5
Views
722
Hi, I'm just looking for a simple way to make a button in excel (via VBA I presume) to toggle a bit in RSLogix 5000. I just got FactoyTalkLinx...
Replies
9
Views
497
Hello, I want to send the data from Rslogix500 to Excel. I created a connection between RSlinx and Excel. The values in Excel automatically...
Replies
5
Views
1,130
Hi guys, Im back again, this time I cannot minimized excel even though i put 6 on the exec mode.. this is my cicode.. Exec("C:\Program...
Replies
1
Views
1,079
Back
Top Bottom