1

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.

image showing functionality

0

3 Answers 3

2

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)&""""

enter image description here

2
  • 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
2

@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''")))
1

=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''"))

3
  • 2
    @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
  • 2
    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.
    – Blindspots
    Nov 7, 2023 at 17:14
  • 1
    Thanks for the positive feedback
    – P.b
    Nov 7, 2023 at 17:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .