Help with IF Function

[ANAND KUMAR]

Dear All,

I need some help on IF function. I have a sheet in below format In the Overall results (2nd Image) section ,I want another formula to calculate pass, needed attention and fail ,this should be done by dividing sample size by errors identified

For ease attaching file herewith,

https://www.sendspace.com/file/gcs2o2


Any help would be appreciated.

 

[ANAND KUMAR]

Can someone help on this pls.

 

[Michael M]

Looks like this example needs to be downloaded.....a lot of users won't do that !!
Can you simply post a SMALL sample of your data here !!

 

[ANAND KUMAR]

Thanks for reply. That's why I attached Images as well.

[TABLE="width: 2023"]
<tbody>[TR]
[TD]No.
[/TD]
[TD]Attribute Tested
[/TD]
[TD]Risk Group
[/TD]
[TD]Sample Size
[/TD]
[TD]SAMPLE
Policy #123456
Sept 14, 2016
[/TD]
[TD]Sample #1
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #2
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #3
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #4
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #5
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #6
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #7
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #8
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #9
Policy #
<Date or Ref ID>
[/TD]
[TD]Sample #10
Policy #
<Date or Ref ID>
[/TD]
[TD]# Errors Identified
[/TD]
[TD]Overall Results (Pass - <95% // Attention Needed 93-94% // Fail >93%)
[/TD]
[/TR]
[TR]
[TD="colspan: 2"]New Policies - General
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Is the application the approved MetLife application?
[/TD]
[TD]Legal/Regulatory
[/TD]
[TD]6
[/TD]
[TD]NA
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Is the application complete with all the required information, including the beneficiary (as applicable)?
[/TD]
[TD]Transaction
[/TD]
[TD]6
[/TD]
[TD]NA
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]NA
[/TD]
[TD]3
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Has the application been logged into the internal system of record to track time to process?
[/TD]
[TD]Legal/Regulatory OR Transaction
[/TD]
[TD]8
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]Fail
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]4
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Has a review been completed to ensure that the information provided by the applicant appears appropriate and meets all applicable local requirements (e.g. AML, FATCA, CRS)?
[/TD]
[TD]Legal/Regulatory
[/TD]
[TD]9
[/TD]
[TD]Fail
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]NA
[/TD]
[TD]Pass
[/TD]
[TD]Fail
[/TD]
[TD]Fail
[/TD]
[TD]Fail
[/TD]
[TD]Fail
[/TD]
[TD]7
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]


For sample size Area - Only Pass & Fail make up the sample size and the challenge is with Overall results only.

 

[Michael M]

Is this what you're after ??
But with such small sample sizes, I'm guessing it will always be a fail !!!

=IF(P5/D5<0.93,"Fail",IF(P5/D5<=0.94,"Attenion Needed","Pass"))

 

[ANAND KUMAR]

Sorry for delay in response!

Yes. That's what I needed.....Thanks ☺.