Nested IF Statement Help

[khlau]

Hello, I am trying to get this nested IF formula to work, however it keeps returning FALSE.

Any help appreciated.

=IF(D3="Monthly",IF(COUNTA(E3)<1,"Action Required",IF(D3="Hub Contact",IF(COUNTA(E3)>1,"Action Required",IF(D3="Bi-Monthly",IF(COUNTA(E3)<>E3/B1,"Action Required","No Action"))))))

So this is based on three formulas:

=IF(D3="Bi Monthly",IF(COUNTA(E3)=$B$1/2,"No Action","Action Required"))
=IF(D3="Monthly",IF(COUNTA(E3)/$B$1<1,"Action Required","No Action"))
=IF(D3="Hub Contact",IF(COUNTA(E3:P3)>1,"Action Required","No Action"))

So for some context - There should be a meeting on average once every two months if the engagement RAG is bi-monthly. So for example if after 4 months has passed then the counta of Apr-18 - Jul-18 should equal 2. Else "Action Required" should be stated in the Action Required this month column.

Likewise, if Monthly, then the COUNTA needs to be = 1 or else Action Required
and if Hub Contact, there should be zero contact. So if COUNTA > 1, then Action required

I've got the formulas working individually in the columns, but would just like to amalgamate these into one.

There is a copy of this worksheet if anyone would like a closer look!

https://nofile.io/f/tuDneSPtVND/Mr.+Excel.xlsx

Thanks for your time.

 

[Johnny C]

Hi I can't access your spreadhseet due to firewalls here.

However ... COUNTA(E3) counts the number of non-blank cells in the range E3 ... if E3 is a single cell surely this can't ever be >1? if you've just shortened it for posting here ignore that

 

[Johnny C]

Just looking at it ...

IF(D3="Hub Contact",IF(COUNTA(E3)>1,"...
This needs an ELSE or it will return FALSE (and as I said will always be false)

 

[khlau]

Hi I can't access your spreadhseet due to firewalls here.

However ... COUNTA(E3) counts the number of non-blank cells in the range E3 ... if E3 is a single cell surely this can't ever be >1? if you've just shortened it for posting here ignore that

Hello, thanks for taking a look - so as the months progress, the formula will be amended to include the next month, e.g. in Period 3, it will be range E3:H3. Hope that makes sense!

 

[Johnny C]

You have a number of nested IFs where there is a 'True' action but no 'False' action.

Try this:
=IF(D3="Monthly",IF(COUNTA(E3)<1,"Action Required",IF(D3="Hub Contact",IF(COUNTA(E3)>1,"Action Required",IF(D3="Bi-Monthly",IF(COUNTA(E3)<>E3/B1,"Action Required","No Action"),"No Action")),"No Action")),"No Action")

 

[khlau]

You have a number of nested IFs where there is a 'True' action but no 'False' action.

Try this:
=IF(D3="Monthly",IF(COUNTA(E3)<1,"Action Required",IF(D3="Hub Contact",IF(COUNTA(E3)>1,"Action Required",IF(D3="Bi-Monthly",IF(COUNTA(E3)<>E3/B1,"Action Required","No Action"),"No Action")),"No Action")),"No Action")

managed to get it all working yesterday, thanks for the help.

 

[khlau]

Wondering if anyone is able to take a look at the below?:

Spent some time looking at the next column, got a headache after a while:

=IF(D10="Monthly",IF(COUNTA(E10:P10)=$B$1,"OK",IF(COUNTA(E10:P10)>$B$1,"Overengaging",IF(COUNTA(E10:P10)<$B$1,"Underengaging",IF(D10="Hub Contact",IF(COUNTA(E10:P10)>1,"Naughty",IF(COUNTA(E10:P10)=0,"OK")),IF(D10="Bi Monthly",IF((COUNTA(E10:OFFSET(E10,0,B8-1)=$B$1/2)),"OK","Action Required")))))))

So basically, in the column I am trying to accomplish whether a budget holder is over-engaged, under-engaged or OK based on column D (whether they are due to meet monthly, bi-monthly, or hub contact).

Based on this:

If Monthly, then The COUNTA of the total range > B1 (number of financial periods), Overengaged (shouldn't be possible as there is only one cell per month).
COUNTA < B1 = Underengaged
COUNTA = B1 = OK

If Hub-Contact: COUNTA of total range <> 0 = Overengaged
COUNTA = 0 = OK

Bi-Monthly:
If the COUNTA of passed periods/2 >B1/2, then Overengaging
If COUNTA of passed periods < B1/2 = Underengaged
If COUNTA of passed periods = B1/2 = OK

Couldn't seem to get this to work either so kinda gave up at end of the workday

Have attached the worksheet for reference. Thanks for taking a look.

https://ufile.io/mofo5 - Worksheet for review.