Formula Help

[Rob_010101]

Hello

=IF(OR(ISBLANK(B3)), "", SUM(B3)*NETWORKDAYS("01/01/2023", "31/12/2023", 'Lists (Hide)'!$D$2:$D$26)/2)

If I enter non-numerical value in column "B" where the above formula sums from, it returns "0".

I was planning to use IFERROR with the above formula to return a message if a non-numerical value is accidentally entered but can't because it's returning "0" instead of a #VALUE! (or another) error code (which I would have expected).

Kind Regards
Chris

 

[StuLux]

Can you not just put data validation on the cell to force user to enter a number?

 

[Rob_010101]

Can you not just put data validation on the cell to force user to enter a number?

Thanks very much

 

[jdellasala]

Hello

=IF(OR(ISBLANK(B3)), "", SUM(B3)*NETWORKDAYS("01/01/2023", "31/12/2023", 'Lists (Hide)'!$D$2:$D$26)/2)

If I enter non-numerical value in column "B" where the above formula sums from, it returns "0".

View attachment 87036

I was planning to use IFERROR with the above formula to return a message if a non-numerical value is accidentally entered but can't because it's returning "0" instead of a #VALUE! (or another) error code (which I would have expected).

Kind Regards
Chris

OR(ISBLANK(B3) is redundant. Only ISBLANK is needed. The sum of non numeric values is 0, so you end up with 0 * any number equals 0.
In the Formula tab, using the Evaluate Formula button would have shown that quickly.

You may want to consider using ISNUMBER instead of ISBLANK.

=IF( NOT(ISNUMBER(B3)), "", SUM(B3) * NETWORKDAYS("01/01/2023","31/12/2023",'Lists (Hide)'!$D$2:$D$26)/2))

That should work. My version is actually "=IF( NOT(ISNUMBER(B3)), "", SUM(B3) * NETWORKDAYS("01/01/2023","12/31/2023",Table1[Date]))", and I reworked it here to match yours.

 

[Fluff]

You may want to consider using ISNUMBER

Why? Surely ISTEXT would be better if you are looking for a text value

@Chris_010101 whilst I would agree that data validation is the best option, for the formula you could use

=IF(ISTEXT(B3),"Please enter a number",IF(B3="", "", SUM(B3)*NETWORKDAYS("01/01/2023", "31/12/2023", 'Lists (Hide)'!$D$2:$D$26)/2))

 

[Fluff]

Just realised that there is also a redundant SUM function in there, it should just be

=IF(ISTEXT(B3),"Please enter a number",IF(B3="", "", B3*NETWORKDAYS("01/01/2023", "31/12/2023", 'Lists (Hide)'!$D$2:$D$26)/2))