IF(AND formula calculates correctly on some Opportunities; miscalculates on others when closed Won

Here’s my formula:
ROUND(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“April”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“March”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“February”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“January”),{Forecast value}/12,0)))),2)

When Opportunities are open, this works correctly every time. When Opportunities are closed Won, it works sometimes and other times it increases the value 10 to 1000 times the actual deal value or in this case, 10 to 1000 times 1/12 of the deal value.
Below the green highlighted is Open. The next three rows are closed Won. Rows 2 and 4 are calculating correctly. You’ll notice the third row is inaccurate. The deal value = $6,000. 1/12 of $6,000 is being miscalculated as $500,000 when closed. First row miscalculates when closed Won also.

Hi Nick,

If your results vary depending on a column value not included in the formula, you should contact monday.com support.

 

Want to take your formulas to the next level? Try the Advanced Formula Booster, the app that reinvents formulas in monday.

  • Create formulas without using the Formula column (and avoid its limitations)
  • Build formulas involving data from the previous item, the next item, the sub-items, the parent item, even items in the same group or the same board.
  • In one formula, update multiple columns from multiple items.

Check our blog for real use cases.

Hey Cavin. I did ask for support, but I tried one other trick which gets me closer. It resolves, or calculates all fields correctly, when I change the 0 at the end of each IF(AND series to " ". But then the rest of the appropriately empty fields show the error icon. Any tip on that front to turn the blank error fields back to 0s?

Try:

IF({Fiscal Year - Cash}<>"CFY",0,
IF(OR({Total Cash-Projected Start Month}="April",{Total Cash-Projected Start Month}="March",{Total Cash-Projected Start Month}="February",{Total Cash-Projected Start Month}="January"),ROUND({Forecast value}/12,2),0))

What if we could break free of the Formula column? and write formulas that update any type of columns? What if a formula could update multiple columns at once? This is possible with the Advanced Formula Booster app.

Bam! That did the trick. Thanks :slightly_smiling_face:

What if I need to add NFY and PFY and other codes that are part of the {Fiscal Year - Cash} column? For instance, I have 24 months, current and next fiscal year, showing across the columns. One January CYF, February CYF and one January NYF, February NYF, etc. (and same for all other months).

In this example, the solution you offered works up to December CYF. What do I need to do to make it work for NYF months?

Your solution:
IF({Fiscal Year - Cash}<>“CFY”,0,
IF(OR({Total Cash-Projected Start Month}=“December”,{Total Cash-Projected Start Month}=“November”,{Total Cash-Projected Start Month}=“October”,{Total Cash-Projected Start Month}=“September”,{Total Cash-Projected Start Month}=“August”,{Total Cash-Projected Start Month}=“July”,{Total Cash-Projected Start Month}=“June”,{Total Cash-Projected Start Month}=“May”,{Total Cash-Projected Start Month}=“April”,{Total Cash-Projected Start Month}=“March”,{Total Cash-Projected Start Month}=“February”,{Total Cash-Projected Start Month}=“January”), ROUND({Forecast value}/12,2),0))

Here’s my original formula, which looks like my first post (which worked/works but did the funny thing with 0s when closed Won). You’ll notice in this column, I need February and January associated with NYF and all other months associated with CYF.

ROUND(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“December”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“November”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“October”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“September”),{Forecast value}/12,0) & (IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“August”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“July”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“June”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“May”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“April”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“CFY”,{Total Cash-Projected Start Month}=“March”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“NFY”,{Total Cash-Projected Start Month}=“February”),{Forecast value}/12,0) &
(IF(AND({Fiscal Year - Cash}=“NFY”,{Total Cash-Projected Start Month}=“January”),{Forecast value}/12,0)))))))))))),2)

Here’s what I tried, and/or need, but it does not work (error):
RESOLVED IT – THANKS!
IF(
{Fiscal Year - Cash} <> “CFY”,
0,
IF(
OR(
{Total Cash-Projected Start Month} = “December”,
{Total Cash-Projected Start Month} = “November”,
{Total Cash-Projected Start Month} = “October”,
{Total Cash-Projected Start Month} = “September”,
{Total Cash-Projected Start Month} = “August”,
{Total Cash-Projected Start Month} = “July”,
{Total Cash-Projected Start Month} = “June”,
{Total Cash-Projected Start Month} = “May”,
{Total Cash-Projected Start Month} = “April”,
{Total Cash-Projected Start Month} = “March”
),
ROUND({Forecast value} / 12, 2),
0
)
)
+
IF(
{Fiscal Year - Cash} <> “NFY”,
0,
IF(
OR(
{Total Cash-Projected Start Month} = “February”,
{Total Cash-Projected Start Month} = “January”
),
ROUND({Forecast value} / 12, 2),
0
)
)
RESOLVED IT – THANKS!

ROUND(
    IF(AND({Fiscal Year - Cash}="CFY", {Total Cash-Projected Start Month}="April"), {Forecast value}/12, 0) +
    IF(AND({Fiscal Year - Cash}="CFY", {Total Cash-Projected Start Month}="March"), {Forecast value}/12, 0) +
    IF(AND({Fiscal Year - Cash}="CFY", {Total Cash-Projected Start Month}="February"), {Forecast value}/12, 0) +
    IF(AND({Fiscal Year - Cash}="CFY", {Total Cash-Projected Start Month}="January"), {Forecast value}/12, 0)
, 2)

This fixes the issue by replacing concatenation (&) with addition (+) to ensure the values are summed correctly, thus preventing miscalculations for closed Won opportunities.