Using a range in a formula

Richard Tillman 260

Hi, I'm new to using a range in a formula, rather than just making a formula for one cell (in terms of that cell) & then copying that cell to other cells (with Excel updating the references). I have this formula in C3:

=if(abs(mround($B3,C$2)-$B3)<=$B$2/2,round($B3/C$2,0) & "=" & mround($B3,C$2),"")

and it does what I want when I copy it to C3:o43. How do I make that into a range formula? (And do I just put that in C3?)

Thank you

Ashish Mathur 101.9K Reputation points Volunteer Moderator

2026-05-21T23:04:28.3533333+00:00

Hi,

Share some data to work with, explain the question and show the expected result.

Answer accepted by question author

Barry Schwarz 5,756

This may be slightly simpler than IlirU's solution

=LET(rr,B3:B43,cc,C2:O2,DROP(REDUCE(0,rr,LAMBDA(s,b,VSTACK(s,DROP(REDUCE(-1,cc,LAMBDA(t,c,HSTACK(t,IF(ABS(MROUND(b,c)-b)<=$B$2/2,ROUND(b/c,0)&"="&MROUND(b,c),"")))),0,1)))),1))

However I think your original solution is much easier to understand and therefore easier to maintain. The only virtue of either of our complex solutions is if the arguments of the IF function are likely to change then the change is confined to a single formula without any need for propagation.

IlirU 2,491 Reputation points Volunteer Moderator

2026-05-22T16:29:40.5933333+00:00

Barry Schwarz,

I don't know where you see the fact that your formula is a little simpler than my formula. But even if it is so, I notice that if in any of the header data (cells C2:O2) the number 0 or any negative number is placed, your formula fails completely, giving no result, while my formula does not produce results only for the column where this data is.

Anyway, it doesn't matter which formula is simpler, but what matters is that the OP finds the solution he is looking for.

BR.
Richard Tillman 260 Reputation points

2026-05-22T16:43:30.3833333+00:00

Is there not a simple way I can use the just the formula in C3, and only change something in it to the range? Like changing mod(A1,3) to mod(A1:A10,3) works for a simple formula, instead of copying it to A2:A10.
Barry Schwarz 5,756 Reputation points

2026-05-22T19:34:51.94+00:00

@Richard Tillman

Not with every function. While ROUND(B3/C2,0) can be expanded to ROUND(B3:B43/C2:02,0) to produce a 41x13 array of values, MROUND cannot. You would have to limit yourself to only those functions which can support array arguments.

But @IlirU raises a couple of interesting points. If the value in C2 is 0, the expression ROUND(B3/C2,0) produces a #DIV/0! error value. Similarly, if the value is negative, the expression MROUND(B3,C2) produces a #NUM! error value for a positive B3. Whether this is an issue depends an the values you are working with.
Dana D 90 Reputation points

2026-05-23T01:59:08.9933333+00:00

=if(abs(mround($B3,C$2)-$B3)<=$B$2/2,round($B3/C$2,0) & "=" & mround($B3,C$2),"")

How do I make that into a range formula?
Richard Tillman 260 Reputation points

2026-05-23T16:59:26+00:00

I see, thank you.

Answer accepted by question author

IlirU 2,491 Volunteer Moderator

User's image

Hi @Richard Tillman ,

Is this the formula you are looking for?

=TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(C2:O2, LAMBDA(col, ARRAYTOTEXT(BYROW(B3:B43, LAMBDA(rw, ARRAYTOTEXT(IF(ABS(MROUND(rw, col) - rw) <= B2 / 2, ROUND(rw / col, 0) & " = " & MROUND(rw, col), "")))))))), ", ", ";"))

Hope this helps.

IlirU

0 comments

1 additional answer

AI answer

2026-05-21T17:38:25.5633333+00:00
The existing formula is already written so it can be filled across and down as a normal (non-array) formula. To apply it to the whole range C3:O43, enter it once in C3, then fill it.

Steps:

Select cell C3 and enter the formula:

=IF(ABS(MROUND($B3,C$2)-$B3)<=$B$2/2,ROUND($B3/C$2,0) & "=" & MROUND($B3,C$2),"")

Use the fill handle to copy it across to column O:

With C3 selected, drag the fill handle (small square at the bottom-right corner of the cell) across to O3.

Fill it down to row 43:

Select the filled range C3:O3.

Drag the fill handle down to row 43 (C43:O43).

Because the formula uses:

$B3 (absolute column, relative row) and $B$2 (absolute row and column), and

C$2 (relative column, absolute row),

it will adjust correctly as it is filled across and down. There is no need to convert it to an array/range formula; Excel’s relative/absolute references and fill behavior are designed for exactly this scenario.

If desired, the Fill command can be used instead of dragging:

Enter the formula in C3.

Select C3:O3, then use Home → Fill → Right.

Select C3:O43, then use Home → Fill → Down (or Ctrl+D).

References:

Use Excel as your calculator

Move or copy a formula in Excel

Fill a formula down into adjacent cells

Switch between relative, absolute, and mixed references

Overview of formulas in Excel

Use cell references in a formula
AI-generated content may be incorrect. Read our transparency notes for more information.
Was this answer helpful?

Share via

Using a range in a formula

1 additional answer

Your answer