0

I am using the formula:

=IF(J1939=0,"NO SALES",((C1939*C1940)+(D1939*D1940)+(E1939*E1940)+(F1939*F1940)+(G1939*G1940)+(H1939*H1940)+(I1939*I1940))/J1939)

I am using the IF function to ensure I do not receive a #DIV/0 error. I am now receiving a #VALUE error in the cell the above formula is entered. Cells I1939 and I10940 are blank and need to stay blank in this particular worksheet.

I am looking for a way to ignore any cells that have #VALUE errors in proceeding cells linked to proceeding functions. For example, in another cell relating to the cell in question, I am using

=IF(J2325=0,"NO SALES",(((J47*J48)+(J426*J427)+(J805*J806)+(J1184*J1185)+(J1563*J1564)+(J1942*J1943))/J2325))

which is also giving me the #VALUE error.

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Owen Fought
  • 1
  • 2
  • Have you considered nesting this `=IF()` into an `=IFERROR()`? – Kyle Mar 06 '15 at 19:49
  • The `#VALUE!` is likely occurring because one or more of the values used within the mathematical operation is text or a textual representation of a number and not a true number. Default cell formatting is left-aligned for text, right-aligned for true numbers and dates. Try `=1*"A"` in a cell. –  Mar 06 '15 at 19:54
  • For this particular case, it is not a problem to receive a "NO SALES" in my first formula. The deeper problem I'm encountering is receiving the #VALUE error in the second (relating) formula. – Owen Fought Mar 06 '15 at 20:12

1 Answers1

2

Try using this formula

=IF(J1939=0,"NO SALES",SUMPRODUCT(C1939:I1939,C1940:I1940)/J1939))

That will give you the same results but ignore text in the referenced cells

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I think you're on the right track. Can you provide a similar formula for my second formula: =IF(J2325=0,"NO SALES",(((J47*J48)+(J426*J427)+(J805*J806)+(J1184*J1185)+(J1563*J1564)+(J1942*J1943))/J2325)) – Owen Fought Mar 06 '15 at 20:14
  • when I evaluate the formula the error comes from "J1942*J1943" portion because cell J1943 is a "NO SALES" value. – Owen Fought Mar 06 '15 at 20:20
  • That one is harder to convert to SUMPRODUCT because they aren't contiguous ranges, why is it just those rows? is there some text that only occurs in rows 47, 426 etc.? One option is to change your "No Sales" to a zero - you can format those cells so that "No sales" still displays while the underlying value is zero – barry houdini Mar 06 '15 at 20:48