Forum Discussion

Sneaky's avatar
Sneaky
Occasional Reader
Jun 07, 2026

Help with Formular to keep constants

Hi

I have 2 rows for percentage values as per this table. C3 will be the value of (C2 + B3) / 2.  However I need to check for an empty value so my calculation is 

=IF($B$3="",0, IF($C$2="",0,($C$2+$B$3)/2)) - I hope that is right,

 ABCDEFG
1  NameNameNameNameName
2  50.25%70.78%60.14%24.86%

53.64%

3Name60.12%55.18%65.45   
4Name45.58%47.91%    


I then need to copy(drag) the cells across and down and provide the correct values, so it uses the same top value and left value.  For example D3 will be =IF($B$3="",0, IF($D$2="",0,($D$2+$B$3)/2)).  C4 will be =IF($B$4="",0, IF($C$2="",0,($C$2+$B$4)/2)) and so which will alter as I drag down or left and it keeps using the correct column value and row value based on what column/row it is in.

What I am doing now does not retain the right column or row value.

I hope I make sense.

Thanks.

excel

2 Replies

  • IlirU's avatar
    IlirU
    Iron Contributor
    Jun 08, 2026

    Sneaky​,

    Below are three formulas that you can use. The first two formulas which are manual formulas are explained quite well by m_tarler​. The third formula is a dynamic formula.

    • First formula in cell C3. Drag it right and down.
    =IF($B3 = "", 0, IF(C$2 = "", 0, (C$2 + $B3) / 2))
    • Second formula in cell C3. Drag it only down.
    =IF($B3 = "", 0, IF(C$2:G$2 = "", 0, (C$2:G$2 + $B3) / 2))
    • Third formula in cell C3. No need to drag it.
    =LET(
     row, B3:B4,
     col, C2:G2,
          --TEXTSPLIT(TEXTJOIN(";",, BYROW(row, LAMBDA(row, ARRAYTOTEXT(
          IF(row = "", 0, IF(col = "", 0, (row + col) / 2)))))), ", ", ";")
)

    Format the cells as percentages.

    Hope this helps.

    IlirU

  • m_tarler's avatar
    m_tarler
    Silver Contributor
    Jun 08, 2026

    So you need to use and not use the $ accordingly.  The $ sign mean that part of the reference is absolute and should not change (when copied or filled) and without a $ means it is relative and should increment/change according to where the formula is copied to.  So try:

    =IF($B3="",0, IF(C$2="",0,(C$2+$B3)/2))

    So basically this will 'lock' column B but not row 3 of $B3 and it will 'lock' row 2 but not column C of C$2

    Alternatively you could also do this all using a single array formula:

    =IF($B$3:$B$4="",0, IF($C$2:$G$2="",0,($C$2:$G$2+$B$3:$B$4)/2))

    and since it is a single array formula there is no copy/fill needed so it really doesn't matter if you include the $ or not 

    2 other things of note or consideration are 

    a) you could consider using IFS or useing AND/OR functions instead of multiple IF.  For example:

    =IF(OR($B3="",C$2=""),0,(C$2+$B3)/2)

    b) with array formula you could also multiply the 2 values and use that as a criteria (assuming 0% should also be ignored)  for example:

    in this case any cell that results in 0 due to the product is shown as blank ("") which includes both columns E (0%) an F (blank).