Excel does not have any built-in way of formatting numbers such that the numbers are displayed in a neatly readable way using SI prefixes, like m for milli (10^-3), k for kilo (10^3), µ for micro (10^-6) etc. Here I provide a VBA function I wrote to fill this need. In addition, I added support for using the prefix notation of Spice circuit simulators where both m and M are interpreted as milli and Meg has to be used to signify mega. Also, u has to be used instead of µ to indicate micro in Spice.
As icing on the cake, I also wrote a function to convert the resulting strings back to numbers.
The functions are called FormatPrefix and ValPrefix. Source code with detailed descriptions of the input arguments are shown further down in this post.
Below is a link to an Excel document with the functions built into a VBA module and a worksheet with an example table of values fed to the function to display its output for a large variation of input arguments:
Here is a screenshot demonstrating the formatting:
The source code is shown below:
Public Function FormatPrefix(value As Double, _
Optional precision As Integer = 3, _
Optional blankZero As Boolean = False, _
Optional space As Boolean = False, _
Optional spice As Boolean = False) As String
' Convert a number to a string, usually with an SI prefix at the end.
'
' Arguments:
' value is the value to be converted.
' Optional precision argument determines number of significant digits to be printed (default 3).
' Optional blankZero argument determines if zero should return the empty string (default false).
' Optional space argument determines if a space should be inserted after the number but before
' a possible prefix (default false).
' Optional spice argument determines if Spice prefixes should be used instead of metric ones
' (default false). The only differences are Meg vs M and u vs µ.
' Values above or equal to 1e15 or below 1e-18 are printed using scientific notation without
' prefix.
' The decimal indicator is always a point, even if the locale says it should be a comma.
'
' Written by Per Magnusson, Axotron, 2016-02-26
' This code is public domain and comes without any warranty. Enjoy!
' Handle the value 0
If value = 0 Then
If blankZero Then
FormatPrefix = ""
Else
If space Then
FormatPrefix = "0 "
Else
FormatPrefix = "0"
End If
End If
Exit Function
End If
' Handle negative numbers
sign = 1
If value < 0 Then sign = -1 ' store the sign value = -value ' make value positive until later End If ' Calculate exponent and mantissa exponent = Int(Log(value) / Log(10#)) mantissa = Round(value / (10 ^ exponent), precision - 1) If mantissa >= 10 Then
' Handle the case where the mantissa is rounded up to 10
exponent = exponent + 1
mantissa = mantissa / 10
End If
exponent3 = 3 * Int(exponent / 3) 'exponent rounded down to nearest multiple of 3
' Find the right prefix
If exponent >= 15 Then
' Too big to represent with the supported prefixes, use scientific notation
prefix = "sci"
ElseIf exponent >= 12 Then
prefix = "T"
ElseIf exponent >= 9 Then
prefix = "G"
ElseIf exponent >= 6 Then
If spice Then
prefix = "Meg"
Else
prefix = "M"
End If
ElseIf exponent >= 3 Then
prefix = "k"
ElseIf exponent >= 0 Then
prefix = ""
ElseIf exponent >= -3 Then
prefix = "m"
ElseIf exponent >= -6 Then
If spice Then
prefix = "u"
Else
prefix = "µ"
End If
ElseIf exponent >= -9 Then
prefix = "n"
ElseIf exponent >= -12 Then
prefix = "p"
ElseIf exponent >= -15 Then
prefix = "f"
ElseIf exponent >= -18 Then
prefix = "a"
Else
' Too small to represent with the supported prefixes, use scientific notation
prefix = "sci"
End If
If prefix = "sci" Then
' Use scientific notation
formatStr = "0"
If precision > 1 Then
formatStr = formatStr & "."
For ii = 1 To precision - 1
formatStr = formatStr & "0"
Next
End If
formatStr = formatStr & "e-0"
prefix = ""
value = sign * mantissa * 10 ^ exponent
Else
decimals = precision - (exponent - exponent3) - 1
If decimals > 0 Then
formatStr = "0."
For ii = 1 To decimals
formatStr = formatStr & "0"
Next
Else
formatStr = "0"
End If
value = sign * mantissa * 10 ^ (exponent - exponent3)
End If
optSpace = ""
If space Then
optSpace = " "
End If
FormatPrefix = Replace(Format(value, formatStr) & optSpace & prefix, ",", ".")
'FormatPrefix = formatStr & optSpace & prefix
End Function
Public Function ValPrefix(str As String) As Double
' Convert a number formatted as a string using FormatPrefix() back to a number.
'
' Arguments:
' str is the string to be converted
' Error checking is very limited.
'
' Written by Per Magnusson, Axotron, 2016-02-26
' This code is public domain and comes without any warranty. Enjoy!
exponent = 0
prefix = ""
strlen = Len(str)
If StrComp(Right(str, 1), "A") >= 0 Then
' There is a letter at the end of the value
ii = 1
While StrComp(Mid(str, strlen - ii, 1), "A") >= 0
ii = ii + 1
Wend
prefix = Mid(str, strlen - ii + 1)
Select Case prefix
Case "T"
exponent = 12
Case "G"
exponent = 9
Case "M"
exponent = 6
Case "Meg"
exponent = 6
Case "k"
exponent = 3
Case "m"
exponent = -3
Case "u"
exponent = -6
Case "µ"
exponent = -6
Case "n"
exponent = -9
Case "p"
exponent = -12
Case "f"
exponent = -15
Case "a"
exponent = -18
Case Else
' Error, handle it somehow?
ValPrefix = "Prefix error"
Exit Function
End Select
End If
ValPrefix = Val(str) * 10 ^ exponent
End Function
An I2C bus (or SMBus) can fail to work for various reasons. A well known reason is that there may be too much capacitance on the bus causing too slow low-to-high transitions. 400 pF is the maximum bus capacitance according to the I2C specification, but this is not necessarily a hard limit in practice. The problem of excessive capacitance can often be remedied by lowering the data rate, decreasing the value of the pull-up resistors or somehow reducing the bus capacitance by changed routing, shortened cables or using a different kind of cable.
This article however describes a different and perhaps less obvious problem that can occur while running an I2C bus in a cable, namely crosstalk, and what to do about it, if it becomes a problem.
Here is a photo of the cross section of the simple telephone cable used in the experiments in this article. The labels show the initial signal assignment that were used to provide both power and an I2C bus to a temperature sensor.
4.7 kohm were used as pull-up resistors. Below is a scope plot of the resulting signals when using 70 cm of cable and a data rate of approximately 200 kHz.
The red arrows show where one signal is disturbed by a fast transition from high to low on the other signal. The green arrow shows that no disturbance can be seen on SCL when SCL is low, despite the fast transition from high to low on SDA. The reason that the signals are only disturbed while they are high is that in the high state, it is only a relatively weak pull-up resistor that keeps the signal at its proper level, so a small amount of current injected into the signal can change the voltage quite a bit. When an I2C signal is low, it is held low by a strong transistor which has an on-resistance of only a few tens of ohms so it would require a current that is two orders of magnitude stronger to cause a disturbance of the same amplitude on a low I2C signal than on a signal in the high state.
If the negative going glitches caused by the crosstalk become large enough, they may cause the bus to fail in various ways as the signals are misinterpreted, so it is important to keep these under control. The I2C specification defines valid high logic levels as being at least 70% of VDD. 70% of 3.3 V is 2.31 V and the glitches in the plot above dip down to this level, which is bad.
Coupling mechanism
The mechanism for coupling from the disturbing signal (the aggressor) to the disturbed signal (the victim) is the capacitance between the wires in the cable. A schematic model of the I2C-bus and cable (drawn using LTspice) is shown below.
R1 and R2 are the I2C pull-up resistors. C1-C4 are the capacitances between adjacent wires in the cable while C5 and C6 are the capacitances between the diagonal wires. C7 and C8 are stray capacitances on the PCBs as well as capacitances at the pins of the ICs. S1 is a switch that models the open-drain output of the driving pin while V1 controls when the switch is closed and when it is open. The values of several of the components are set as parameters (within curly braces) to make it easier to modify them and adapt them to the actual values of a specific instance of the circuit. The capacitances shown in the schematic are reasonably representative of the real circuit discussed above.
Below are the simulated waveforms of the SCL and SDA signals when the I2C pull-up resistors are stepped from 1k via 2k2 and 4k7 to 10kohms.
One can note that the amplitude of the glitches and the steepness of the edges match the measurement pretty well for the 4k7 pull-up resistor (the next slowest traces). This supports that the model is fairly accurate.
We can see that the initial glitch in the SDA waveform has (almost) the same amplitude regardless of the the value of the pull-up resistors. This might not be expected, but is a consequence of the fact that the initial part of the step is dominated by the capacitive voltage division in the circuit. The capacitive divider consists of C1 which injects charge into the parallel combination of C4, C5 and C7. Right after the step, the voltages on the capacitors start to recover and how quickly this happens is determined by the value of the pull-up resistor, so the 1k resistor gives a ten times quicker recovery than the 10k resistor. But note that it does not significantly reduce the magnitude of the glitch.
There is also a little crosstalk happening during the slower positive edge of the aggressor. This crosstalk is much less of a problem since it has a positive amplitude and thus either increases the already positive amplitude of the victim signal or does not affect a low victim signal since that is actively being pulled low and is thus almost immune to the disturbance.
Reduced pull-up resistances
A lower value of the pull-up resistors did not seem to do much good according to the simulation, but let’s anyway verify this in reality. Below is the same measurement as before, but with 2.2 kohm pull-up resistors.
As predicted by the simulation, the amplitude of the glitches remain the same, while the positive edges have become steeper.
Modified cable configuration
So, we could not reduce the amplitude of the crosstalk by the old standard trick of reducing the pull-up resistance. So is there something else that we can try?
Since the coupling mechanism is capacitive, we would like to reduce the capacitance between the aggressor and victim. This can actually be done without changing cables if we change which wire is used for what signal. To do this, the I2C signals are put on diagonal instead of adjacent wires as follows:
(If it had instead been a four-wire ribbon cable, putting the I2C signals on the outer wires and power and GND on the two inner had been the way to go. In a twisted-pair cable, the I2C signals should be twisted with power or GND and absolutely not with each other.)
The resulting crosstalk is shown in the plots below for 4.7k and 2.2k pull-ups.
As can be seen, the glitches have been substantially reduced and are now harmless. This can also be simulated by just changing the simulation schematic slightly to move the aggressor diagonally from the victim:
The resulting waveforms are shown below.
The simulation matches the measurement well also in this case.
So the method of placing the I2C signals far away from each other in the cable is apparently effective, just as expected.
A less elegant possibility is to add more capacitance between the I2C signals and GND, provided the bus capacitance is not already too large. This will affect the capacitive voltage division in a positive manner and reduce the crosstalk by increasing the capacitance from victim to GND instead of reducing the capacitance between aggressor and victim. The cable reassignment above actually did both reduce the aggressor-victim capacitance as well as increase the victim-to-GND (and power) capacitance, so it made use of both mechanisms.
Cable length influence
Another question is how the length of the cable affects the crosstalk. By examining the schematic models of the cables, one can realize that all the cable capacitances (C1-C6) are proportional to the length of the cable. As mentioned above, the amplitude of the crosstalk is controlled by capacitive voltage division. All of the capacitances involved in the division, except for the small external capacitances to GND (C7 and C8), grow linearly with the length of the cable, so as long as the cable capacitances dominate over the stray capacitances to GND, the length of the cable should have only a very minor impact on the amplitude of the crosstalk.
Below is an oscilloscope plot of an I2C bus that is run through 2.7 m of cable.
As expected, the amplitude of the crosstalk has remained essentially the same while the edges are less steep due to the added capacitance.
Summary and recommendations
I2C is particularly sensitive to capacitive crosstalk since the signals are often not driven by strong drivers, but instead by weak pull-up resistors.
Glitches caused by crosstalk can cause an I2C bus to malfunction.
Cables often have a large capacitance between at least some of the wires.
Reducing the value of the I2C pull-up resistors does not have any significant effect on the crosstalk and is therefore not an effective way of controlling it.
I2C signals should be placed in a cable such that they have as little capacitance as possible to aggressor signals, i.e. to signals with fast high-to-low transitions (like other I2C signals).
Capacitance between I2C wires and power and GND wires help reduce crosstalk (but at the expense of higher bus capacitance, and thus slower transitions and reduced maximum speed for a given value of pull-up resistor).
In twisted pair cables, the I2C signals should be paired with power or GND wires, not with other I2C signals.
In ribbon cables, the I2C signals should only be adjacent to power or GND wires.
The length of the cable does normally not affect the amplitude of the crosstalk very much. (As long as the cable is long enough so that the cable capacitance dominates over other bus capacitances.)
Longer cables do of course add more capacitance and thus reduce the steepness of the positive edges, which can reduce the maximum speed and/or require reduced pull-up resistances.
It is a very good idea to use an oscilloscope to take a look at the I2C signals in any new design to see if they look good or need to be improved.
I hope this article has provided some intuition about – and insight into – I2C crosstalk in cables and what to do to control it.
This blog post describes an issue I am having with the XBee 2.2.3 Python package where the callbacks that are supposed to happen when new packets are received suddenly stop. I managed to find the reason for this as well as an at least temporary solution.
A bit of background
I just tried to add a second node to my small ZigBee network (now consisting of a coordinator and two routers) and ran into a bit of trouble. The new node has a new version of the XBee radio, namely XB24CZ7PIT-004. This has replaced the previous product XB24-ZB from Digi and it has a new firmware which simultaneously supports coordinator/router/end point. So one does not have to upload a firmware specific to the role the radio is playing in the network. Maybe it has other subtle differences as well. I do not know if the new radio version is relevant to the problems I am having, but I thought I’d better mention it.
The problem
I use a callback function to receive the data from the XBee, but the problem is that when I added the new XBee (as a router), I just got one or a few packets back from the coordinator (which is the one I talk to using Python) before callbacks stopped. I discovered that a new error callback feature had been added in 2.2.2, so I made use of this to try to troubleshoot:
# Callback function that runs when XBee errors are detected
def xbee_error(e):
print "XBee error!"
print e
xbee = ZigBee(ser, callback = receive_data, error_callback = xbee_error)
This gave me the following printout before receive_data callbacks ceased:
XBee error!
'Unrecognized response packet with id byte \xa1'
So apparently a “new” kind of packet with frame type A1 has started to show up for some reason unknown to me. This turns out to be a “Route Record Indicator”. I have not looked into why this happens, but since the A1 frame type is not defined in the api_responses dictionary in zigbee.py and there is a new except-clause with a break in the definition of run() in base.py, I think the thread that receives data from the XBee and calls the callback function thereby terminates when such an unrecognized frame id is received.
I do not think this is appropriate behavior in this case. The thread should not die just because an A1 frame ID appears. I solved it quickly and in an ugly manner by commenting out the troublesome break in base.py:
def run(self):
"""
run: None -> None
This method overrides threading.Thread.run() and is automatically
called when an instance is created with threading enabled.
"""
while True:
try:
self._callback(self.wait_read_frame())
except ThreadQuitException:
# Expected termintation of thread due to self.halt()
break
except Exception as e:
# Unexpected thread quit.
if self._error_callback:
self._error_callback(e)
<b>#break # Commented out by Per M 2016-01-06</b>
There are probably better ways to solve this. Maybe the A1 and other possible frame IDs (see the API frame specifications section in the XBee User Guide) should be added to api_responses in zigbee.py.