Excel: How to add two numbers that has unit prefixes?

Question

I'm trying to add numbers that have the unit prefixes appended at the end (mili, micro, nano, pico, etc). For example, we have these two columns below:

Obviously doing something like =A2+A3+A4 and =B2+B3+B4 would not work. How would you resolve this? Thanks

Is there a finite set of units/unit prefixes your solution needs to support? — Marc, Jan 27 '23 at 18:33
Needs more details. What is the expected output for the data you provided? — BigBen, Jan 27 '23 at 18:38
Do you want the units converted? Column 1 without unit conversion would be 721.203 + 1.368 + 119.551, but with units conversion (all into ms) it would be 721.203 + 0.001368 + 0.119551. It would be odd to add these numbers without converting to a common unit, but that is all the posted answers have shown so far. — Steven Rumbalski, Jan 27 '23 at 19:31
@StevenRumbalski yes, I would like them converted to account for the difference in units, to get an accurate addition — tadm123, Jan 27 '23 at 19:41
@BigBen the output can be shown in any units (but preferably miliseconds to make it more clean) — tadm123, Jan 27 '23 at 19:44
These units belong in a custom number format... not in the value itself. — Mathieu Guindon, Jan 27 '23 at 19:49
@P.b nice, I knew there'd be some formula from hell leveraging all the crazy Turing-complete new capabilities that could do it - my point was that the units are metadata that is separate from the values. If you want to show "25ms" in a cell and do any kind of math with it, you enter 25 and format it to append "ms". In this case you'd insert helper columns for N1 & N2 where you'd extract the units and values, and then another one to convert it all into the same unit, and sum up that column. — Mathieu Guindon, Jan 28 '23 at 02:08
Instead you can **substitute** the **right** 2 characters & convert them to numbers, then you can easily add it. — Manoj, Jan 28 '23 at 04:40
@MathieuGuindon the initial TEXTSPLIT solution does exactly that: splitting the value and the text and multiplying the value based on the unit. Instead of using helper cells it used helper names inside LET. — P.b, Jan 28 '23 at 09:59
And it only works if your Excel supports dynamic arrays and LET and all the crazy new stuff. — Mathieu Guindon, Jan 28 '23 at 14:16

David Leal · Accepted Answer · 2023-01-28T00:40:09.013

4

Assuming you don't have excel version constraints, per the tags listed in your question. Put all the suffixes as delimiters inside {} delimited by a comma as follow in TEXTSPLIT, then define the conversion rules in XLOOKUP. We use SUBSTITUTE(col, nums, "") as input of XLOOKUP to extract the unit of measure.

=BYCOL(A2:B4, LAMBDA(col, LET(nums, 1*TEXTSPLIT(col,{"ms","us"},,1),
 units, XLOOKUP(SUBSTITUTE(col, nums, ""), {"us";"ms"},{1;1000}),
 SUM(nums * units))))

The above formula converts the result to a common unit of microseconds (us), i.e. to the lower unit, so milliseconds get converted by multiplying by 1000. If the unit of measure was not found it returns #N/A, it can be customized by adding a fourth parameter to XLOOKUP. If you want the result in milliseconds, then replace: {1;1000} with {0.001;1} or VSTACK(10^-3;1) for example.

If you would like to have everything in seconds, you can use the trick of using power combined with the XMATCH index position, to generate the multiplier. I took the idea from this question: How to convert K, M, B formatted strings to plain numbers?, check the answer from @pgSystemTester (for Gsheet, but it can be adapted to Excel). I included nanoseconds too.

=BYCOL(A2:B4,LAMBDA(col,LET(nums,1*TEXTSPLIT(col,{"ms","us"},,1),
  units, 1000^(-IFERROR(XMATCH(RIGHT(col,2), {"ms";"us";"ns"}),0)),
  SUM(nums * units))))

Under this approach, seconds is the output unit, because it is not part of the XMATCH lookup_array input argument, the multiplier will be 1 (as a result of 1000^0), so no units or seconds (s) will be treated the same way.

Notes:

In my initial version I used INDEX, but as @P.b pointed out in the comments, it is not really necessary to remove the second empty column, instead, we can use the ignore_empty input argument from TEXTSPLIT. Thanks
You can use TEXTBEFORE instead of TEXTSPLIT, as follows: TEXTBEFORE(A2:A4,{"ms","us"})

edited Jan 28 '23 at 00:40

answered Jan 27 '23 at 18:33

David Leal

6,373
4
29
56

2

I was assuming the solution needed to account for adding differing units, e.g. microseconds + milliseconds, based on the unit specified. Does yours do that? – Marc Jan 27 '23 at 18:37
1

@BigBen, true, but original poster mentioned adding the column values, e.g., `=A2+A3+A4`. – Marc Jan 27 '23 at 18:40
2

Ah good point. Either way, needs more detail. – BigBen Jan 27 '23 at 18:40
1

@Marc I was trying to respond to the ask about adding numbers with suffixes, having a way to remove them and to sum it. The OP mentions different units, but no indication of any conversion mapping to consider. Let's see if the OP provides more clarity around that. At least here it covers how to remove the suffix and do some operation. Thanks – David Leal Jan 27 '23 at 18:52
1

Why is INDEX used? – P.b Jan 27 '23 at 18:59
1

@P.b true we can use the ignore_empty from `TEXTSPLIT`. I will update it, thanks – David Leal Jan 27 '23 at 19:04
Hello @DavidLeal yes I would like them to be converted to account for the difference in units. – tadm123 Jan 27 '23 at 19:40
@tadm123 thanks for your clarification, please check my updated answer. It converts everything to the lower unit of measure (microseconds). I added also an approach for converting everything to seconds. I hope it helps – David Leal Jan 27 '23 at 20:45
@DavidLeal Thanks, can I have a formula to convert a single column instead of doing it both at the same time? – tadm123 Jan 27 '23 at 20:46
Since this formula only works with there's two columns in conjunction with each other.. – tadm123 Jan 27 '23 at 21:45
yes, @tadm123 you can use the following and adjust the range via `rng` name value: `=LET(rng, A2:A4, nums, 1*TEXTSPLIT(rng,{"ms","us"},,1),units, XLOOKUP(RIGHT(rng,2), {"us";"ms"},{1;1000}), SUM(nums * units))` – David Leal Jan 27 '23 at 21:46
I saw your last comment, there is no need to do it column by column, if the columns are separated, you can use the same approach, wrapping the `BYCOL` input with `CHOOSECOLS` for example: `=BYCOL(CHOOSECOLS(A2:Z4, 1,3,5,7), LAMBDA(col, LET(nums, 1*TEXTSPLIT(col,{"ms","us"},,1), units, XLOOKUP(RIGHT(col,2), {"us";"ms"},{1;1000}), SUM(nums * units))))`, but the result will be consecutive columns – David Leal Jan 27 '23 at 21:50
One last question if you don't mind man, I already accepted your answer, how would you account if there's a "s"? (seconds) in the case of a single column. – tadm123 Jan 27 '23 at 21:51
@tadm123 I included this answer in my updated answer before, please take a look and let me know. Thanks – David Leal Jan 27 '23 at 21:52
1

Borrowing from @DavidLeal, I tried this, which appears to work if the value, e.g., "75ms" appears in cell A1: `=1*TEXTSPLIT(A1,{"s","ms","us"},,1)*XLOOKUP(SUBSTITUTE(A1,TEXTSPLIT(A1,{"s","ms","us"},,1),""), {"s";"ms";"us"},{1;1000;1000000})`. BTW David, as an onlooker, I got an education here on some functions I haven't used, so thanks! – Marc Jan 27 '23 at 21:52
1

@Marc this is the approach I suggested too (similar maybe, but not the same, I am not sure), to remove `RIGHT` in case of having a unit of measure of a single character, but yes using `RIGHT` is more restrictive. – David Leal Jan 27 '23 at 21:56

Excel: How to add two numbers that has unit prefixes?

1 Answers1