Replacing Null values formula pulling back null

[Lucyxbite]

Hi, 

I am using the data attached and trying to get the formula below to bring back results which will replace the null with the amount stated in "total available sales" however the formula says to have worked but it is still bringing back "null"

[Diesal]

I suspect that it's down to the fact that you cannot divide by zero (undefined) therefore the formula returns null

[Lucyxbite]

I was hoping the if statement I used would recognsie it cant divide by 0 which would bring back Null meaning that that null would be replaced with the value in total available sales in the first case 242

[Diesal]

Ah I see 🙂

Try this then:
IF [Average Weekly Sales] = 0 THEN [Total available after sales] ELSE [Total available after sales] / [Average Weekly Sales] ENDIF

[SeanAdams]

Hey @Lucyxbite  as a few folks have said - you're getting a divide by zero error which is a different situation.

A better way to do this is to explicitly check for null; then explicitly check for divide by zero.

if (isnull([Total available after sales]) or isnull([average weekly sales))

then 0

else

     if [average weekly sales] = 0 //the divide by zero case

then 0

else

     ([Total available after sales] / [ Average weekly sales])

endif