Hi Please help Me out for the Making Excel Formula !!

[qurat]

Please help me out for formula making !!

if BG2-B5 complete full quarter earn so apply percentage 3% (i.e. BI13), then BH2-B5 complete fully 2 quarter so apply 9% (i.e. BJ12) and so one till 3%(i.e. Bp13)

Similarly, in second case if BG2-B7 complete 1 month only so i can't apply percentage 3% (i.e. BI13) because 3% is the fully earn quarter so what would be the formula for 1 month earn show in 1st lag and remaining would be show in the 8th quarter, and the remaining quarters are same as above paragraph.

Red Highlighted is the main confusing for me !!

Attached file is for the reference !!

Thanks in advance
Regards

 

[Zot]

It is hard for people to recreate the sheet to test/verify solution. It helps if you could use XL2BB to copy and paste your table here. See guide here

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[qurat]

					2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11	2	5	8	11
				31-Dec-05	31-Mar-06	30-Jun-06	30-Sep-06	31-Dec-06	31-Mar-07	30-Jun-07	30-Sep-07	31-Dec-07	31-Mar-08	30-Jun-08	30-Sep-08	31-Dec-08	31-Mar-09	30-Jun-09	30-Sep-09	31-Dec-09	31-Mar-10	30-Jun-10	30-Sep-10	31-Dec-10	31-Mar-11	30-Jun-11	30-Sep-11	31-Dec-11	31-Mar-12	30-Jun-12	30-Sep-12	31-Dec-12	31-Mar-13	30-Jun-13	30-Sep-13	31-Dec-13	31-Mar-14	30-Jun-14	30-Sep-14	31-Dec-14	31-Mar-15	30-Jun-15	30-Sep-15	31-Dec-15	31-Mar-16	30-Jun-16	30-Sep-16	31-Dec-16	31-Mar-17	30-Jun-17	30-Sep-17	31-Dec-17	31-Mar-18	30-Jun-18	30-Sep-18	31-Dec-18	31-Mar-19	30-Jun-19	30-Sep-19	31-Dec-19	31-Mar-20	30-Jun-20	30-Sep-20	31-Dec-20	31-Mar-21
Risk Start	Risk End	Issue	Gross		EARNED PROPORTION																																																											0.12585499
Date	Date	Date	Premium		Q1 2006	Q2 2006	Q3 2006	Q4 2006	Q1 2007	Q2 2007	Q3 2007	Q4 2007	Q1 2008	Q2 2008	Q3 2008	Q4 2008	Q1 2009	Q2 2009	Q3 2009	Q4 2009	Q1 2010	Q2 2010	Q3 2010	Q4 2010	Q1 2011	Q2 2011	Q3 2011	Q4 2011	Q1 2012	Q2 2012	Q3 2012	Q4 2012	Q1 2013	Q2 2013	Q3 2013	Q4 2013	Q1 2014	Q2 2014	Q3 2014	Q4 2014	Q1 2015	Q2 2015	Q3 2015	Q4 2015	Q1 2016	Q2 2016	Q3 2016	Q4 2016	Q1 2017	Q2 2017	Q3 2017	Q4 2017	Q1 2018	Q2 2018	Q3 2018	Q4 2018	Q1 2019	Q2 2019	Q3 2019	Q4 2019	Q1 2020	Q2 2020	Q3 2020	Q4 2020	Q1 2021
01-Jan-19	31-Dec-20	01-Nov-19	11,147,155	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	348,349	1,045,046	1,741,743	2,438,440	2,438,440	1,741,743	1,045,046	348,349	-
01-Jan-19	31-Dec-20	01-May-19	20,583,015	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	-
01-Mar-19	28-Feb-21	01-May-16	20,583,015	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	221,553	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	421,666
01-Jan-18	31-Dec-19	01-May-16	20,583,015	730	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	-	-	-	-	-
					-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-						31.000	120.000	212.000	304.000	395.000	486.000	578.000	670.000	30.000
																																																									if BG2-B5 complete full quarter so apply percentage 3%(i.e BI13), then BH2-B5 complete fully 2 quarter so apply 9%(i.e BJ12) and so one till 3%(i.e Bp13)
																																																									Similarly, in second case if BG2-B7 complete 1 month only so i can't apply percentage 3%(i.e BI13) because 3% is the fully earn quarter so what would be the formula for 1 month earn show in 1st lag and remaining would be show in the 8th quarter, and the remianing quarters are same as above paragraph.
																																																											Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
																																																									Proposed		3%	9%	16%	22%	22%	16%	9%	3%
																																																										20,583,015	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219
																																																											221,553	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219

 

[Zot]

You have an error in your formula, a circular reference

 

[qurat]

You have an error in your formula, a circular reference

Where is the error ? Can you highlight it ?

				31-Dec-05	31-Mar-06	30-Jun-06	30-Sep-06	31-Dec-06	31-Mar-07	30-Jun-07	30-Sep-07	31-Dec-07	31-Mar-08	30-Jun-08	30-Sep-08	31-Dec-08	31-Mar-09	30-Jun-09	30-Sep-09	31-Dec-09	31-Mar-10	30-Jun-10	30-Sep-10	31-Dec-10	31-Mar-11	30-Jun-11	30-Sep-11	31-Dec-11	31-Mar-12	30-Jun-12	30-Sep-12	31-Dec-12	31-Mar-13	30-Jun-13	30-Sep-13	31-Dec-13	31-Mar-14	30-Jun-14	30-Sep-14	31-Dec-14	31-Mar-15	30-Jun-15	30-Sep-15	31-Dec-15	31-Mar-16	30-Jun-16	30-Sep-16	31-Dec-16	31-Mar-17	30-Jun-17	30-Sep-17	31-Dec-17	31-Mar-18	30-Jun-18	30-Sep-18	31-Dec-18	31-Mar-19	30-Jun-19	30-Sep-19	31-Dec-19	31-Mar-20	30-Jun-20	30-Sep-20	31-Dec-20	31-Mar-21
Risk Start	Risk End	Issue	Gross		EARNED PROPORTION
Date	Date	Date	Premium		Q1 2006	Q2 2006	Q3 2006	Q4 2006	Q1 2007	Q2 2007	Q3 2007	Q4 2007	Q1 2008	Q2 2008	Q3 2008	Q4 2008	Q1 2009	Q2 2009	Q3 2009	Q4 2009	Q1 2010	Q2 2010	Q3 2010	Q4 2010	Q1 2011	Q2 2011	Q3 2011	Q4 2011	Q1 2012	Q2 2012	Q3 2012	Q4 2012	Q1 2013	Q2 2013	Q3 2013	Q4 2013	Q1 2014	Q2 2014	Q3 2014	Q4 2014	Q1 2015	Q2 2015	Q3 2015	Q4 2015	Q1 2016	Q2 2016	Q3 2016	Q4 2016	Q1 2017	Q2 2017	Q3 2017	Q4 2017	Q1 2018	Q2 2018	Q3 2018	Q4 2018	Q1 2019	Q2 2019	Q3 2019	Q4 2019	Q1 2020	Q2 2020	Q3 2020	Q4 2020	Q1 2021
01-Jan-19	31-Dec-20	01-Nov-19	11,147,155	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	348,349	1,045,046	1,741,743	2,438,440	2,438,440	1,741,743	1,045,046	348,349	-
01-Jan-19	31-Dec-20	01-May-19	20,583,015	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	-
01-Mar-19	28-Feb-21	01-May-16	20,583,015	731	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	221,553	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	421,666
01-Jan-18	31-Dec-19	01-May-16	20,583,015	730	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	-	-	-	-	-
					-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
																																																											Q1	Q2	Q3	Q4	Q5	Q6	Q7	Q8
																																																									Proposed		3%	9%	16%	22%	22%	16%	9%	3%
																																																										20,583,015	643,219	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219		20,583,015
																																																											221,553	1,929,658	3,216,096	4,502,534	4,502,534	3,216,096	1,929,658	643,219	421,666	20,583,015

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: https://chandoo.org/forum/threads/hi-please-help-me-out-for-the-making-excel-formula.45567/
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[qurat]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Hi Please help Me out for the Making Excel Formula !!
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Sure, i got your point. Thanks

 

[Zot]

When I copy paste you table it showed Circular References: F5 on bottom left corner