SOLVED

Need Help with IF Formula in Calculated Column

Copper Contributor

Need Help with IF Formula in Calculated Column

Hi everyone!

I am hoping you can please help me.  I am calculating a burn rate in a SharePoint List in a calculated column (titled Burn).  I am trying to return a blank value in the Burn column if the Funding Remaining column is blank or if there is an error in the column titled Burn.  Below is a brief description of the columns I am using.

Funding Remaining

Jun Invoice

Jul Invoice

Aug Invoice

The below formula works fine if the funding remaining column is blank, but I have tried to get rid of the errors (predominantly brought on when the trying to average the Jun, Jul, and Aug invoice columns) when they are blank.

=IF(ISBLANK([Funding Remaining (As of last invoice)]),"",[Funding Remaining (As of last invoice)]/AVERAGE([Aug '23 Invoice (Est.)],[Jul '23 Invoice (Est.)],[Jun '23 Invoice (Est.)]))

2 Replies
best response confirmed by Brian_Sarpy (Copper Contributor)
Solution

Re: Need Help with IF Formula in Calculated Column

Hi @Brian_Sarpy,

i am not sure, if i got your requirements correctly

``=IF(ISBLANK([Funding Remaining (As of last invoice)]),"",[Funding Remaining (As of last invoice)]/AVERAGE([Aug '23 Invoice (Est.)],[Jul '23 Invoice (Est.)],[Jun '23 Invoice (Est.)]))``

works fine for me without an error, except if all invoice columns are empty

If that is your issue, then you can just wrap your formula into another if clause and check if the sum of the invoice columns is greater zero

``=IF([Jun '23 Invoice (Est.)]+[Jul '23 Invoice (Est.)]+[Aug '23 Invoice (Est.)]>0,IF(ISBLANK([Funding Remaining (As of last invoice)]),"",[Funding Remaining (As of last invoice)]/AVERAGE([Jun '23 Invoice (Est.)],[Jul '23 Invoice (Est.)],[Aug '23 Invoice (Est.)])),"")``

Best Regards,
Sven

Re: Need Help with IF Formula in Calculated Column

This worked perfectly.  Thank you so much for your help!

1 best response

Accepted Solutions
best response confirmed by Brian_Sarpy (Copper Contributor)
Solution

Re: Need Help with IF Formula in Calculated Column

Hi @Brian_Sarpy,

i am not sure, if i got your requirements correctly

``=IF(ISBLANK([Funding Remaining (As of last invoice)]),"",[Funding Remaining (As of last invoice)]/AVERAGE([Aug '23 Invoice (Est.)],[Jul '23 Invoice (Est.)],[Jun '23 Invoice (Est.)]))``

works fine for me without an error, except if all invoice columns are empty

If that is your issue, then you can just wrap your formula into another if clause and check if the sum of the invoice columns is greater zero

``=IF([Jun '23 Invoice (Est.)]+[Jul '23 Invoice (Est.)]+[Aug '23 Invoice (Est.)]>0,IF(ISBLANK([Funding Remaining (As of last invoice)]),"",[Funding Remaining (As of last invoice)]/AVERAGE([Jun '23 Invoice (Est.)],[Jul '23 Invoice (Est.)],[Aug '23 Invoice (Est.)])),"")``

Best Regards,
Sven