dschickson
New member
- Joined
- May 5, 2015
- Messages
- 2
- Reaction score
- 0
- Points
- 0
Hi I need help producing an array formula that will produce the following results when entered in B3 and dragged across to D3:
I have an array formula which is nearly working:
{=SUM(OFFSET($B1,0,0,1,COLUMNS($B$1:B$1))*IF(COLUMNS($B$1:B$1)>1,PRODUCT(1+OFFSET($B2,0,0,1,COLUMNS($B$1:B$1)-1)),1)*B2)}
The issue is that the above formula is applying the same compound interest growth to all amounts rather than the historic amounts...so in D3 instead of getting the correct result of:
=((B1*(1+B2)*(1+C2))*D2)+((C1*(1+C2))*D2)+(D1*D2)
you get the incorrect formula
=((B1*(1+B2)*(1+C2))*D2)+((C1*(1+B2)*(1+C2))*D2)+((D1*(1+B2)*(1+C2))*D2)
I logically know how this should be written mathematically BUT cannot figure it out in excel...
Assuming we are solving for D3 - I need to be able to do the following:
Amount = Array X = {1000,0,1000}
% Rate = Array Y = {0.01,0.02,0.03}
Results = ((X1*(1+Y1)*(1+Y2)) + (X2*(1+Y2)) + X3)*Y3
Any ideas? Or ways of reconciling the result by subtracting some other formula...
THANKS SO MUCH for any and all help.
A | B | C | D | |
1 | Amount | 1,000 | 0 | 1,000 |
2 | % Rate | 1% | 2% | 3% |
3 | Formula | =(B1*B2) | =((B1*(1+B2))*C2)+(C1*C2) | =((B1*(1+B2)*(1+C2))*D2)+((C1*(1+C2))*D2)+(D1*D2) |
I have an array formula which is nearly working:
{=SUM(OFFSET($B1,0,0,1,COLUMNS($B$1:B$1))*IF(COLUMNS($B$1:B$1)>1,PRODUCT(1+OFFSET($B2,0,0,1,COLUMNS($B$1:B$1)-1)),1)*B2)}
The issue is that the above formula is applying the same compound interest growth to all amounts rather than the historic amounts...so in D3 instead of getting the correct result of:
=((B1*(1+B2)*(1+C2))*D2)+((C1*(1+C2))*D2)+(D1*D2)
you get the incorrect formula
=((B1*(1+B2)*(1+C2))*D2)+((C1*(1+B2)*(1+C2))*D2)+((D1*(1+B2)*(1+C2))*D2)
I logically know how this should be written mathematically BUT cannot figure it out in excel...
Assuming we are solving for D3 - I need to be able to do the following:
Amount = Array X = {1000,0,1000}
% Rate = Array Y = {0.01,0.02,0.03}
Results = ((X1*(1+Y1)*(1+Y2)) + (X2*(1+Y2)) + X3)*Y3
Any ideas? Or ways of reconciling the result by subtracting some other formula...
THANKS SO MUCH for any and all help.