Want to verify cells are equal, but getting reversed 'true' 'false' values in IF() statement...

Options
Stacey C
Stacey C ✭✭✭✭
edited 08/02/22 in Formulas and Functions

I have a large SmartSheet that contains deposit information for my company plus others we do billing for. Since payments come in long after the service date in healthcare, I may get an outlier payment for a company that is no longer active in current year, but need to process.

I've created a cell 'All Deposits' that is the sum of [Credit Amount]:[Credit Amount]. I've created Sub-Totals for each known company by using SumIf statements for each company. I've then created a Formula Check to ensure the sum of the Sub-Totals is equal to the sum of All Deposits to make sure I am not overlooking an outlier company payment from years past.

Despite values of both 'All Deposits' and sum of 'Sub-Totals' looking identical on screen at $4,837,354.40, when I write an IF() statement to verify the two cells are equal and return OK or Calc Error response, I get 'Calc Error' even though the data appears true. I have checked formatting for cells, decimal places beyond hundredths, moved to different columns to avoid circular references but nothing works. I've even tried using <> and reversing my desired output, but still get the false response. Any thoughts?

Here are my formulas:

Deposit1=SUM([Credit Amount]:[Credit Amount]) //value = $4,837,354.40//

Deposit2:Deposit7=SUMIF(Company:Company, [Sub Headers]@row, [Credit Amount]:[Credit Amount])

Deposit8=Sum(Deposit2:Deposit7) //value=$4,837,354.40//

Alert Message=IF(Deposit1 = Deposit8, "OK", "ERROR!") //value="ERROR!"//

Tags:

Answers

  • Stacey C
    Stacey C ✭✭✭✭
    Options

    UPDATE: I think I've solved my own problem. By using the ROUND() function to two decimal places for both currency fields I am trying to verify, I was able to get my IF statement to work as expected. I have to assume that the difference has something to do with my imported data, and will continue to test as I add new rows for weekly credits and company deposits, but this seems to have resolved my issue for now.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Stacey C

    I hope you're well and safe!

    Excellent!

    Glad you got it working!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Please support the Community by marking your post with the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!