Forum Discussion

colleen hourie's avatar
colleen hourie
Copper Contributor
Jul 12, 2018

Problem with IF Function

I am trying to calculate a number of cells and need to use the IF function.  The calculation is 

IF H13>7.74, H13,SUM(H12,H13,H14, etc.) but if H10:I11 is blank then put H13.  Here is the formula that I tried but is not working:

=IF(H13>7.74,H13,SUM(H12,H13,H14,H15,H16,H17,H18,H19,H20,H21)-IF(LEN(H10:I11)=0,H13))

But I get the #VALUE!  error. Any ideas how to fix this?

Does it matter that the "Blank" cells are a Time, as opposed to a number?

excel
Formulas and Functions

2 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jul 14, 2018

    In addition,

    1) you may change

    LEN(H10:I11)      on
SUM(H10:I11)

    to check if all these cells are empty

    2) Better to avoid cells merging, that could be side effect. Alternative is:

    select couple of your cells->Ctrl+1->Alignment->Horizontal->Center Across Selection

    That's not one click job as with merging, but much more reliable.

  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Jul 14, 2018

    First of all, if you need to sum H12 to H20, use SUM(H12:H20) instead of listing all the cells separated with commas.

     

    The error you see is related to the LEN function: that function gives you the number of character of a single cell. This means that "LEN(A1)" if A1 is "cat" is 3, if it's "frog" it's 4 and so on.

    It has nothing in common with times and hours, but the important part is that that LEN function works on a single cell only.

     

    Try changing LEN with COUNTA and see if I got what you need.

Resources