Forum Discussion

nannerb1115's avatar
nannerb1115
Copper Contributor
Jun 28, 2023
Solved

Sum with blank cells

This may be an easy answer but I can't seem to figure it out for some reason. I want to average 4 cells but sometimes the cell will not be filled. I want the blank cells to always represent the number 150. Can I create some formula using an IF function of some sort? 

 

Thank you

excel
Formulas and Functions
  • OliverScheurich's avatar
    OliverScheurich
    Jun 28, 2023

    nannerb1115 

    =AVERAGE(IF(ISBLANK(A1:A4),150,A1:A4))

    You can try this formula for the AVERAGE. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

    =SUM(IF(ISBLANK(A1:A4),150,A1:A4))

    You can try this formula for the SUM. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

2 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 28, 2023

    nannerb1115 

    =AVERAGE(IF(ISBLANK(A1:A4),150,A1:A4))

    You can try this formula for the AVERAGE. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

    =SUM(IF(ISBLANK(A1:A4),150,A1:A4))

    You can try this formula for the SUM. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

     

Resources