I want to pull out/extract data through Excel Formula

I have a portfolio of almost 20,000 Customers as mentioned in Excel Sheet. I want to pull out/extract data through Excel Formula for the followings:

1). How to Mark Status of each customer in Excel Sheet as "Matured", "Pre-Matured" or Blank "Outstanding" with the help of Excel Formula.

2). How to Mark Pre-Matured if any Customer want to avail Pre-Maturity option in the excel sheet with the help of Excel Formula.

=IF(G3<=$L$1,"Matured",IF(G3>$L$1," ","Pre-Matured"))

3). How to Pull out data of Customers those have become eligible for 6 month Coupon (in the case of 3 years & 5 Years only) with the help of Excel Formula.

=IF(AND(I3=3-Years,I3=5-Years),EDATE(F3,6)," ")

Kindly Help me.

No Excel 2016

Anybody please help me.

Hi,
1. Is this the formula you applied in column "L" ? what is the problem with the formula?
2. when does a customer considered "pre-matured"?
3. See column "H" :
=IF(OR(I3="3-Years",I3="5-Years"),EDATE(F3,6)," ")

I want to extract data on three basis out of 20,000 customers. The basis are as follows:

How to pull out Status of each customer in same Excel Sheet and on another sheet like as "Matured", "Pre-Matured" or Blank "Outstanding" with the help of Excel Formula.

Also how to Pull out/extract data of Customers those have become eligible for 6 month Coupon (3 years & 5 Years only) with the help of Excel Formula. In this case I am using currently filter for example: if today is 9th Nov-2023, then how many customers would be claiming 6 month Coupon today.

these two issues are there. Current formula is not sufficient and another formula in column "H" is also not working. Please help me by giving any appropriate formula for both cases. Regards.

Originally Posted by Limor_OP

Hi,
1. Is this the formula you applied in column "L" ? what is the problem with the formula?
2. when does a customer considered "pre-matured"?
3. See column "H" :
=IF(OR(I3="3-Years",I3="5-Years"),EDATE(F3,6)," ")

"Pre-Matured or Pre-Maturity Encashment" if any Customer want to avail Pre-Maturity option after one month from the date of issuance and on any date before date of maturity anytime. I want to mark it in the excel sheet with the help of Excel Formula and also want to extract summary for three categories on any date 1). Customers Matured, 2). Customers Pre-Matured & Customer Outstanding (neither matured nor pre-matured)".

Originally Posted by Limor_OP

3. See column "H" :
=IF(OR(I3="3-Years",I3="5-Years"),EDATE(F3,6)," ")

In this case if we are talking about 3 Years Certificate then I would have 3*2=6 Coupons on future dates exact after 6 months;
Same in the case of 5 Years I would provide 5*2=10 Coupons. I want an Excel Formula which shall provide how many customers are having coupon today.

Originally Posted by Limor_OP

Hi,
If you want to count the "MATURED" cases for example - then you can use :
=COUNTIF(L3:L12,"MATURED")

Or you can make a pivotable like in the attached, filter the status you are interested to show and count the number of statuses.
Hope this helps.

"I have already made PIVOT but the problems is I need to mark each day the customers those are maturing or pre-maturing. Also I have to filter manually customers from the PIVOT those are likely to have coupon today. To avoid error in manual marking I want to apply excel formula on PIVOT to have details of customers those are having coupon date today in PIVOT".

May I use this Formula:
=IF(OR(I3="3-Years",I3="5-Years"),IF($L$1-F3=EDATE(F3,6),"1ST Coupon",IF($L$1-F3=EDATE(F3,12),"2ND Coupon",IF($L$1-F3=EDATE(F3,18),"3RD Coupon",IF($L$1-F3=EDATE(F3,24),"4TH Coupon",IF($L$1-F3=EDATE(F3,30),"5TH Coupon",IF($L$1-F3=EDATE(F3,36),"6TH Coupon","NO Coupon")))))))

Hey,
your formula looks great!
so is this resolved for you or do you need any further help?

No this formula is not working & I need help for setting following conditions to pull out coupon data on =Today() basis:

Condition One: IF Issue date of Certificate is 3 Years & Maturity date is less than or equal to (within) 36 months then provide current coupon date
Condition Two: IF Issue date of Certificate is 5 Years & Maturity date is less than or equal to (within) 60 months then provide current coupon date

I am attaching excel sheet again Please help me to fix it for above conditions. Regards & Thanks.

Hi,
please see attached condition 1 in column G
=IF(AND(U3="3-years",IF(CHOOSE(1,S3<$X$1)=TRUE,DATEDIF(S3,$X$1,"M"),DATEDIF($X$1,S3,"M"))<=36),T3,"")

condition 2 in column H:
=IF(AND(U3="5-years",IF(CHOOSE(1,S3<$X$1)=TRUE,DATEDIF(S3,$X$1,"M"),DATEDIF($X$1,S3,"M"))<=60),T3,"")

Let me know if the numbers make sense, the 2 formulas can be combined....

Thanks for kind attention and support. Please fix this one as well:
=IF(OR(U7="3-Years",U7="5-Years"),EDATE(F7,6),0)
in this formula if Certificate is 3 years then it must provide coupon date till 6th Coupon and in 36 months
In this formula if Certificate is 5 years then it must provide coupon date till 10th Coupon and in 60 months

Regards,

Originally Posted by Shahmeer

Thanks for kind attention and support. Please fix this one as well:
=IF(OR(U7="3-Years",U7="5-Years"),EDATE(F7,6),0)
in this formula if Certificate is 3 years then it must provide coupon date till 6th Coupon and in 36 months
In this formula if Certificate is 5 years then it must provide coupon date till 10th Coupon and in 60 months

Regards,

1).Please make it one formula for both conditions
2).Further I want to pull out coupon date of any certificate as at =today() basis. So need to fix it like if I give date then Formula give me coupon of respective customer from Table.

Thanks.

Here is the combined conditions:

Please Login or Register  to view this content.

Originally Posted by Shahmeer

1).
2).Further I want to pull out coupon dateof any certificate as at =today() basis. So need to fix it like if I give date then Formula give me coupon of respective customer from Table.
.

Dont think I understand this one.
please apply manually the expected results , Thanks.

Actually I need to have a formula to apply on PIVOT to fetch those customers who have coupon date today based on Issue date and Tenor (3 Years & 5 Years). Its pre-requisite from start of this thread. Kindly, do the needful. Thanks

Originally Posted by Limor_OP

Here is the combined conditions:

Please Login or Register  to view this content.

The Above formula is giving error #NAME?
what to do?

=IF(OR(U7="3-Years",U7="5-Years"),EDATE(F7,6),0)
in this formula if Certificate is 3 years then it must provide coupon date till 6th Coupon and in 36 months
In this formula if Certificate is 5 years then it must provide coupon date till 10th Coupon and in 60 months

You have not answered this? I think you are busy. Should I ask for help from others????

Originally Posted by Shahmeer

The Above formula is giving error #NAME?
what to do?

How about now?

Originally Posted by Shahmeer

Actually I need to have a formula to apply on PIVOT to fetch those customers who have coupon date today based on Issue date and Tenor (3 Years & 5 Years). Its pre-requisite from start of this thread.

see column R
=IF(SUM(1*(H3:Q3=$X$1))=0,"",B3)

If we are standing on 09th Nov, 2013 then we must apply a Formula to have current coupon not first coupon. The formula must provide coupon based on today's date. Please look into.

what do you mean today's coupon? Please fill in the expected results manually as I don't think I understand your intent,

Intent is clear apply both conditions in such a way that coupon No. wise according to the date of filtering information. Just simple.

If it's so simple - perhaps you are able to do it yourself : ))

Originally Posted by Shahmeer

=IF(OR(AND(S3="3-Years",Q3<=EDATE(F3,36),S3="5-Years",Q3<=EDATE(F3,60))),EDATE(F3,6),0)

Condition One: IF Issue date of Certificate is 3 Years & Maturity date is less than or equal to (within) 36 months then provide current coupon date
Condition Two: IF Issue date of Certificate is 5 Years & Maturity date is less than or equal to (within) 60 months then provide current coupon date

Thanks for your kind & prompt support.

How to find out which coupon is due today????? This was my initial and important problem in this exercise please do a favor. thanks

Originally Posted by Limor_OP

If it's so simple - perhaps you are able to do it yourself : ))

I wish I could otherwise you would not be bothered here

Originally Posted by Limor_OP

If it's so simple - perhaps you are able to do it yourself : ))

After all you are a genius Forum Expert

Again Formula in Condition 1 Giving Name# error

Originally Posted by Limor_OP

see column R
=IF(SUM(1*(H3:Q3=$X$1))=0,"",B3)

Again Value# & Name# error in Column G Condition 1
????????

Originally Posted by Limor_OP

Dont worry I will help you, but please keep in mind that I'm only volunteering here, and do it in my spare time ...
patience is a valuable thing... : )

See column R:

=IFERROR(INDEX($A$2:$Q$2,AGGREGATE(15,6,(COLUMN($H$2:$Q$2))/(H3:Q3=$X$1),1)),"")

I appreciate your valuable effort and cooperation with me. Hatts Off.

Again Value# & Name# error in Column G Condition 1
????????

Please do something. Thanks

Originally Posted by Shahmeer

Again Value# & Name# error in Column G Condition 1
????????

see post #36 . It should work..

Please combine these formulas and make a one for 3 years and 5 years:

=IF(OR(U7="3-Years",U7="5-Years"),EDATE(F7,6),0)
=IF(H7=0,0,EDATE(H7,6))

Thank you

Try this:
IF(OR(U7="3-Years",U7="5-Years"),EDATE(F7,6),IF(H7=0,0,EDATE(H7,6)))

its not working.