Nested IF Formula

[Freeman2022]

Hello,

I'm having trouble with a nested formula, PLEASE HELP

If Type is Edit and Video is Y then Points=3
If Type is Edit and Video is N then Points=1
If Type is Published and Video is Y then Points=4
If Type is Published and Video is N then Points=2

See attached File

 

[ranman256]

just make a function in a module then the usage is (or similar):
=getPoints(C1,C2,C3,C4)

Sub getPoints(ByVal pvEdit, ByVal pvPublish As Boolean, ByVal pvVideo)
Select Case True
   Case pvEdit = "Edit" And pvVideo = "Y"
     getPoints = 3
   Case pvEdit = "Edit" And pvVideo = "N"
     getPoints = 1
   Case pvPublish = "Published" And pvVideo = "Y"
     getPoints = 4
   Case pvPublish = "Published" And pvVideo = "N"
     getPoints = 2
End Select
End Sub

 

[orienip]

Good afternoon,

An IFS formula wrapped in an IFERROR formula can do the trick:

=IFERROR(IFS(AND(A2="Y",B2="Edit"),3,AND(A2="N",B2="Edit"),1,AND(A2="Y",B2="Published"),4,AND(A2="N",B2="Published"),2),0)

 

[Fluff]

Hi & welcome to MrExcel.
Another option

=IF(B2="edit",1,2)+IF(A2="Y",2,0)

@orienip The OP has xl2016 & so doesn't have the IFS function.

 

[Eric W]

Also:

=(A2="Y")*2+(B2="Published")+1

 

[Freeman2022]

Thanks everyone...All of the solutions worked. Is there a way to mark all as solutions, I'm pretty new to this, but wanted to ensure everyone got credit. Thanks again

 

[Fluff]

Glad we could help & thanks for the feedback.

Is there a way to mark all as solutions

Nope, only one post can be marked as the solution.

All of the solutions worked

Are you still using xl2016?

 

[Freeman2022]

Glad we could help & thanks for the feedback.

Nope, only one post can be marked as the solution.


Are you still using xl2016?

No, I'm actually using Excel 365

 

[Fluff]

In that case I would suggest updating your profile.

 

[Freeman2022]

Hi & welcome to MrExcel.
Another option

=IF(B2="edit",1,2)+IF(A2="Y",2,0)

@orienip The OP has xl2016 & so doesn't have the IFS function.

In that case I would suggest updating your profile.

Thank you, just updated to 365 in my profile.