# Return the only value from a range that contains one populated cell

I'm new to Excel. I have a Laser measurer recording results in a spreadsheet:

• Each row in `D:G` contains a single populated column and the remaining columns are empty.
• The column that is populated varies by row.

I have formula that converts the value to a string listing feet and inches.

For example, in row `10` where the value is in column `F`, my formula is:

``````=INT(F10/12)&"' "&MOD(F10,12)&"""
``````

This formula works but I have to know the column number in advance.

I want to adapt the formula to work for all rows regardless which column is actually populated.

Your description is not entirely clear. Assuming you have really empty cells and only one numerical value in your range, you can adapt the following pragmatic solution for your purposes:

``````=INT(SUM(A2:D2)/12)&"' "&MOD(SUM(A2:D2);12)&""""
``````

Please note the screenshot is from an German Excel 2019 version. You must at least replace the semicolon with a comma.

``````=INT(SUM(A2:D2)/12)&"' "&MOD(SUM(A2:D2),12)&""""
``````

• Thanks that's exactly what I wanted!!! Nov 4, 2023 at 20:14
• @Billibong13 You're welcome. If an answer has helped you solve your problem feel free to mark the answer as accepted. You should also consider voting it up Nov 4, 2023 at 20:51

@P.b's single-row formula is great!. Below is an array formula approach that leverages it but uses BYROW to extend it to multiple rows. It is no better than their MMULT array formula, I simply enjoy making formulas as short as possible.

# Formula

``````=BYROW(D14:G16, LAMBDA(r, TEXT(SUM(r)/1.2, "#0' 0''")))
``````

`=TEXT(SUM(D14:G14)/1.2,"#0' 0''")`

Or spilling in Office 365:

``````=LET(f,D14:G16,
TEXT(MMULT((f/1.2),SEQUENCE(COLUMNS(f),,,0)),"#0' 0''"))

``````
• @BlindSpots It's alright. BYROW is nice too. I'm a big fan of MMULT. If you're looking for short: `tocol(column(f)^0)` I believe the step 0 argument in the sequence calculates more efficient, but for this anything suits.
– P.b
Nov 7, 2023 at 17:10
• Even better! TY! I upvoted you but someone's downvote put you at 0 maybe because of the typo. Hopefully, they will reverse their vote now that it's corrected. Nov 7, 2023 at 17:14
• Thanks for the positive feedback
– P.b
Nov 7, 2023 at 17:49