Google Sheet formula error when added further condition

0
votes

Trying to add a condition in the following formula that IF(F3:F<DATE(2020,6,30),13 whenever i tried to add it gives me an error.

Your help will be highly appreciated

=ArrayFormula(if(len(G3:G),ROUND(IF(LEN(F3:F),IF(G3:G="Confirmed",13 - MONTH(DATEVALUE(TEXT(F3:F,"mmm")&" 1"))) * 13/12,IF(G3:G="Probation",0)))),""))

I broken the formula and make it work for me but how to do the same thing with above one

=IF(AND(F3:F<DATE(2020,6,30),G3:G="Confirmed"),13,(13 - MONTH(DATEVALUE(TEXT(F3:F,"mmm")&" 1"))) * 13/12
)

Sheet link https://docs.google.com/spreadsheets/d/1IGSRMfqDODklJdPS4_TIMlIZJSM2JXqf_4pWOGjav4I/edit#gid=0

1
google-sheets
Where do you want to add the condition? Also, can you share a copy of your sheet? – Jason E.
Updated my question @Jason E. Please have alook on attached sheet – Strenuous

1 Answers

1
votes

Issues

1.) You placed your "Probation" condition outside an IF statement does generating the error of "Wrong number of arguments..."

2.) AND formula cannot be used inside an ARRAYFORMULA because it will end the whole array together which will generate unexpected result.

Solution

1.) You can place your "Probation" condition before the "Confirmed"&DATE condition right after the IF(LEN(F3:F)..

2.) Instead of using AND. You can multiply both conditions which will return 1 if both conditions are TRUE.

You can use the formula below. This is working as expected on my end:

=ArrayFormula(if(len(G3:G),ROUND(IF(LEN(F3:F),IF(G3:G="Probation",0,IF(F3:F<DATE(2020,6,30)*(G3:G="Confirmed"),13,(13 - MONTH(DATEVALUE(TEXT(F3:F,"mmm")&" 1"))) * 13/12))),"")))