Invalid formula error using IF AND

Himesh
Himesh ✭
in Formulas and Functions

Hello,

I'm in the process of converting an excel spreadsheet into a smartsheet, but I've run into a problem. I'm trying to split 80% of a cost over a duration and then take a two month break before showing the final 20% on the third month (month breaks should appear blank). The first part works, but I can't figure out how to do the two month break + show the 20% after the break.

Here's the formula I have so far: "=IFERROR(IF(AND($[Month Helper Column]$1 >= [Start Date]@row, $[Month Helper Column]$1 <= [Finish Date]@row), [80%]@row / [Duration (Months)]@row, IF($[Month Helper Column]$1 = DATE(YEAR([Finish Date]@row), MONTH([Finish Date]@row) + 3, 0), [20%]@row, "")), "N/A")"

Here's the screenshot:

image.png

So, for example in the first row, the October and November columns should be blank and the December column should have the final 20%, but right now the October, November and December columns all have: #INVALID VALUE.

Any help would be much appreciated.

Thanks,

Himesh

Tags:
· Share on FacebookShare on Twitter

Answers

  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    Hello @Himesh

    It's possible that the error is occurring because you are specifying day 0 in your DATE function.

    Try:

    =IFERROR(IF(AND($[Month Helper Column]$1 >= [Start Date]@row, $[Month Helper Column]$1 <= [Finish Date]@row), [80%]@row / [Duration (Months)]@row, IF($[Month Helper Column]$1 = DATE(YEAR([Finish Date]@row), MONTH([Finish Date]@row) + 3, 1), [20%]@row, "")), "N/A")

    · Share on FacebookShare on Twitter
  • Himesh
    Himesh ✭
    https://community.smartsheet.com/discussion/comment/438993#Comment_438993

    Hiya, thanks, but still the same error unfortunately. Is there anything else that could be causing it?

    · Share on FacebookShare on Twitter
  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    @Himesh

    Try splitting it into 2 IF statements to test each condition. If you are getting your 80/20 results correctly, then it has to do with how the function is set up.

    If you don't, then it's possible that the column properties are preventing you from comparing those values.

    It could be beneficial to use the net days function instead of the actual dates within the conditions.

    · Share on FacebookShare on Twitter
  • Himesh
    Himesh ✭

    https://community.smartsheet.com/discussion/comment/439007#Comment_439007

    It's definitely the last part of the if statement that's causing the problem. I've changed it a little bit and added another helper column [Finish Date Month End] so it's an exact match to the month helper column, but I'm getting an: #UNPARSEABLE error now. Here's the formula: =IF($[Month Helper Column]$7 = MONTH([Finish Date Month End]@row) + 3), [20%]@row, "").

    Any idea what I'm doing wrong here? The last part of the formula should basically do this: If the Month Helper column is three months greater than the Finish Date Month End column then show the 20% value and if not then leave it blank. I think the error is stemming from the +3, but I'm not sure how else I'm supposed to add it.

    image.png

    · Share on FacebookShare on Twitter
  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    @Himesh

    Looks like you are trying to compare a number to a date.

    Maybe this will work,

    =IF(MONTH($[Month Helper Column]$7) = MONTH([Finish Date Month End]@row) + 3), [20%]@row, "")

    · Share on FacebookShare on Twitter
  • Himesh
    Himesh ✭
    https://community.smartsheet.com/discussion/comment/439182#Comment_439182

    Still doesn't work, but it's fine. I've managed to find a roundabout way of doing it by adding three months to the Finish Date Month End column ("=IFERROR(DATE(YEAR([Finish Date]@row), MONTH([Finish Date]@row) + 3, 1), DATE(YEAR([Finish Date]@row) + 1, 1, 1)) - 1")) and then matching it on the month columns using "IF($[Month Helper Column]$7 = [Finish Date Month +3]@row, [20%]@row, ""))"

    This works in conjunction with the 80% breakdown over the duration formula, but there's another problem I've encountered with a Month Helper column formula: "=IF(MONTH([Month Helper Column]7) = 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1) - 1, DATE(YEAR([Month Helper Column]7), MONTH([Month Helper Column]7) + 2, 1) - 1)"

    What I need is for each cell to populate the end of the next month, but it breaks down after month 11. Is a fix for this?

    image.png

    Thanks for the help.

    · Share on FacebookShare on Twitter
  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭
    edited 06/05/24

    @Himesh

    Nice job.

    Is it possible that it is due to the reference changing? Or is that something you intended? I only ask because it's what you started with.

    =IF(MONTH([Month Helper Column]$7) = 12, DATE(YEAR([Month Helper Column]$7) + 1, 1, 1) - 1, DATE(YEAR([Month Helper Column]$7), MONTH([Month Helper Column]$7) + 2, 1) - 1)

    Also, this says if the month is equal to 12. You could try changing it to greater than 12, then add 1 to the year. Then it will hopefully include month 12.

    MONTH([Month Helper Column]7) > 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1)

    · Share on FacebookShare on Twitter
  • Himesh
    Himesh ✭
    https://community.smartsheet.com/discussion/comment/439237#Comment_439237

    Hi,

    So, I'm getting a #UNPARSEABLE error with this formula: =MONTH([Month Helper Column]7) > 12, DATE(YEAR([Month Helper Column]7) + 1, 1, 1).

    With regards to the first big formula - the $ reference was needed for the Month Helper Column since the helper month needed to be absolute, but the Finish Date Month +3 column did not. No problem with this one as it works with the modified formula.

    I think everything will work smoothly as long as I can get the Month Helper Column to populate the next month per cell (so getting a version of the above formula to work).

    Thanks for all the help!

    · Share on FacebookShare on Twitter
  • MichaelTCA
    MichaelTCA ✭✭✭✭✭✭

    @Himesh

    Well as little as it was, no problem!

    What is the column property for that helper column?

    I have sheets that analyze rolling time frames and the function for each date is different. In this case, I can't use a column function and using additional helpers to tell the function what to add was excess work and unnecessary.

    Writing in the function was the easiest, fastest, and most robust way I could set it up. BUT, these sheets are only set up to analyze 18 months worth of data, no more and no less (which is the biggest downfall).

    I start with =MONTH()

    Next row is =MONTH()+1, then MONTH()+2, and so on.

    It is beneficial to have at least two sheets when analyzing data. 1 with the source data and 1 with the consolidated data/summaries.

    · Share on FacebookShare on Twitter
  • Himesh
    Himesh ✭
    https://community.smartsheet.com/discussion/comment/439295#Comment_439295

    The column property of the month helper column is Date. Original plan was to have the date on top of the month financial forecast columns, but I ran into an issue there since Smartsheet doesn't allow for dates and financials via formulas to be in the same column.

    I've found a workaround regarding rolling monthly end dates for the helper column. Just used the EOMONTH end date formula in Excel to a ridiculous degree (31/01/2100) and then pasted it into the smartsheet helper column. Not the most advanced solution, but it does the job as I doubt I'll ever need to forecast that far in advance😅.

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!