Need help with finding initial deposit amount in excel

[Ria_Ko]

Hello everybody. Hope all are well.

I am trying to work with indirect function to find the initial deposit amount by extracting values from 6 other worksheets tha have the same format but somehow i am not able to get to the correct formula.
Could you please help me with the formula as its quite confusing to me.

There is a table provided for each interest rate value in worksheets ( there is one table for each interest rate and I put one of them right below the table in the attachment for your view - example "Rate_500" for the 5.00%)
I tried using =SUMPRODUCT((INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$B$3:$B$6")=Scenarios!E54)*(INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$C$2:$G$2")=Scenarios!D54)*(INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$C$3:$G$6"))) but this formula gave me a reference error.

Thankyou,

Ria

 

[jasonb75]

I don't even know where to start trying to work out what you want to do with that formula! The screen capture is of no use due to not having any row or column references to compare to the formula.

Starting with this part, INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$B$3:$B$6")

What is in Rate_500!A1?

Where range(s) should the formula be returning.

Please post examples using xl2bb (green button at the top of the reply box), not screen captures or external links.

 

[Ria_Ko]

Hi jasonb

I don't even know where to start trying to work out what you want to do with that formula! The screen capture is of no use due to not having any row or column references to compare to the formula.

Starting with this part, INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$B$3:$B$6")

What is in Rate_500!A1?

Where range(s) should the formula be returning.

Please post examples using xl2bb (green button at the top of the reply box), not screen captures or external links.

Hi jasonb75,
Thank you for trying to help me. I will share some range captures with the help of xl2bb.

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	5.00%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$5,530	$7,840	$7,440	$5,940	$7,510
4	(Down	1800	$5,850	$6,160	$7,990	$7,300	$7,110
5	Left	2200	$5,270	$7,900	$6,250	$5,750	$6,070
6	Column)	2700	$6,950	$5,860	$7,420	$7,930	$6,170
Rate_500

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	5.50%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$7,620	$7,540	$6,660	$6,120	$7,520
4	(Down	1800	$5,940	$6,720	$7,720	$6,860	$7,210
5	Left	2200	$6,060	$7,930	$7,600	$5,940	$5,980
6	Column)	2700	$6,430	$5,960	$6,400	$5,560	$5,810
Rate_550

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	6.00%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$5,260	$6,350	$6,810	$5,650	$6,970
4	(Down	1800	$7,860	$6,200	$5,820	$5,870	$5,200
5	Left	2200	$7,730	$6,240	$6,980	$5,920	$5,190
6	Column)	2700	$5,120	$5,040	$7,440	$5,640	$5,740
Rate_600

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	6.50%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$5,100	$6,270	$6,700	$6,410	$7,600
4	(Down	1800	$6,280	$6,110	$7,600	$6,740	$6,090
5	Left	2200	$7,240	$7,490	$5,130	$6,240	$5,910
6	Column)	2700	$7,940	$5,960	$5,450	$7,070	$6,870
Rate_650

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	7.00%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$6,150	$7,690	$6,230	$6,440	$7,160
4	(Down	1800	$7,350	$5,030	$7,780	$6,250	$6,690
5	Left	2200	$5,610	$7,680	$5,130	$7,260	$7,350
6	Column)	2700	$6,550	$5,380	$6,330	$7,020	$6,920
Rate_700

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	A	B	C	D	E	F	G
1	7.50%	<-- Rate	Number of Years (Across Top Row)
2		Purchase Price (PV) in middle	4	5	6	7	8
3	Pmt per Year	1500	$6,240	$5,320	$7,500	$5,770	$5,220
4	(Down	1800	$5,950	$7,680	$5,450	$5,500	$6,120
5	Left	2200	$7,420	$6,480	$5,480	$6,430	$5,480
6	Column)	2700	$7,020	$7,590	$5,280	$5,240	$6,560
Rate_750

_47a2829bc81cd461f790402b1090fb2e_C4-Final-Assignment-v2.xlsx
	B	C	D	E	F	G
53	Account Number	Interest Rate	Number of Years	Additional Payments	Initial Deposit	Final Account Balance
54	1	6.50%	4	1500	#REF!
55	2	5.00%	6	1800
56	3	5.00%	7	1500
57	4	7.50%	7	2200
58	5	5.50%	6	2200
59	6	6.00%	7	1500
60	7	7.00%	6	2700
61	8	5.50%	5	1800
62	9	6.50%	4	1800
63	10	6.00%	5	1800
64	11	6.00%	4	1500
65	12	6.50%	4	2200
66	13	7.00%	6	2200
67	14	6.00%	7	2200
68	15	6.50%	8	2700
69	16	7.50%	8	1800
70	17	6.00%	6	2700
71	18	6.00%	6	1800
72	19	6.00%	8	1500
73	20	6.50%	7	1500
74	21	6.50%	7	1800
75	22	7.00%	4	2700
76	23	5.50%	6	2700
77	24	5.50%	5	2200
78	25	7.50%	8	2700
79	26	6.00%	6	2200
80	27	7.50%	7	1800
81	28	7.50%	4	2200
82	29	5.00%	5	1500
83	30	6.50%	5	2200
Scenarios

Cell Formulas
Range	Formula
F54	F54	=SUMPRODUCT((INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$B$3:$B$6")=Scenarios!E54)*(INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$C$2:$G$2")=Scenarios!D54)*(INDIRECT("Rate_"&Rate_500!A1*10000&"!Rate_500!$C$3:$G$6")))
B55:B83	B55	=B54+1

I want to get the initial deposit amount in cell F54 in the screenshot attached.

 

[jasonb75]

Is this what you need?

=INDEX(INDIRECT("Rate_"&TEXT(C54*10000,"000")&"!C3:G6"),match(E54,{1500,1800,2200,2700},0),D54-3)

 

[Ria_Ko]

Yes

Is this what you need?

=INDEX(INDIRECT("Rate_"&TEXT(C54*10000,"000")&"!C3:G6"),match(E54,{1500,1800,2200,2700},0),D54-3)

Yes, just what i needed. Thank you so much for the help, really appreciate your time and effort.
Take Care and have a good day!