summarize results over a large list of sets of assumptions

[nidzela]

hello everybody,

(my first post, have mercy!)

i've asked my students to create a simple pattern in excel: given a starting ammount of money (c), a rate of interest (i) and a certain number of years , they have to find the final ammount after compounding them year per year (it's a basic relative and absolute formula practice).

for example, let's take c=250 $, i=2% and n=3 years. they should reach this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]0[/TD]
[TD]250 (=$c)[/TD]
[TD]5 (=$c*$i)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]255 (=B1+C1)[/TD]
[TD]5.10 (=B2*$i)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]260.10 (=B2+C2)[/TD]
[TD]5.20 (=B3*$i)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]265.30 (=B3+C3)[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

the point is that i've prepared also a table with DIFFERENT STARTING VALUES for each student, so everyone has to reach his different solution in a different row:

[TABLE="class: grid, width: 400, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]c[/TD]
[TD]i[/TD]
[TD]n[/TD]
[/TR]
[TR]
[TD]Student 1[/TD]
[TD]1500[/TD]
[TD]2.7%[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Student 2[/TD]
[TD]350[/TD]
[TD]1.9%[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Student 3[/TD]
[TD]6800[/TD]
[TD]3.1%[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

is there a way to build ALL SOLUTIONS in a new column, beside these starting values?
well... of course there are ways to get that, i'm thinking of VB, but i'd like to avoid it. i also know well how to use OFFSET or INDEX to find the right cell for each case, but the problem is how to introduce each set of assumptions quickly, and how to write down the solution automatically. i also took a look on scenarios, but they seem awful to create (29 students, and 2 more exercises to summarize the same way...).

i'm using latest Excel version (Pro 2013).
thanks in advance for having a look!

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe something like this...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr]
[tr][td]

1​

[/td][td] [/td][td]

c​

[/td][td]

i​

[/td][td]

n​

[/td][td]

Result​

[/td][/tr]


[tr][td]

2​

[/td][td]

Student 1​

[/td][td]

1500​

[/td][td]

2,70%​

[/td][td]

5​

[/td][td]

1713,73​

[/td][/tr]


[tr][td]

3​

[/td][td]

Student 2​

[/td][td]

350​

[/td][td]

1,90%​

[/td][td]

2​

[/td][td]

363,43​

[/td][/tr]


[tr][td]

4​

[/td][td]

Student 3​

[/td][td]

6800​

[/td][td]

3,10%​

[/td][td]

7​

[/td][td]

8420,14​

[/td][/tr]


[tr][td]

5​

[/td][td]

Student 4​

[/td][td]

250​

[/td][td]

2,00%​

[/td][td]

3​

[/td][td]

265,30​

[/td][/tr]
[/table]

Formula in D2 copied down
=B2*(1+C2)^D2

Hope this helps

M.

 

[nidzela]

Formula in D2 copied down
=B2*(1+C2)^D2

M.

thanks, Marcelo!

your formula is correct, and this means that i explained the situation quite properly (fine, i was worried about being able to communicate it).
but it is not what i am searching for, because your single-calculation "jumps" directly to the final result, and this may not be the same as an iterative quest involving a large sequence of intermediate calculations. in other words:

B2*(1+C2)^D2 and OFFSET($B$1;n;0) may be different when n is large enough, because of increasing error at each step (assume offset applied to sheet where the exercise table is located).

i really need to seek for all those possibly-wrong-values, because my students will find them at the bottom of their exercises instead of the correct theorical ones! and here arises the complexity of my quest: you have to get the values at the bottom of variable tables... not only in lenght (easily solved with offset, for example) but also in starting values of c and i. that's what pushed me into the scenario idea, but it looks wrong to apply.

 

[Marcelo Branco]

Sorry, i'm not understanding what you need - how and where to put the results (steps) for each student .

Hope someone else can help you.

M.

 

[Marcelo Branco]

See if this helps


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

c​

[/TD]
[TD]

i​

[/TD]
[TD]

n​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Student 1​

[/TD]
[TD]

1500​

[/TD]
[TD]

2,70%​

[/TD]
[TD]

5​

[/TD]
[TD]

1500​

[/TD]
[TD]

1540,5​

[/TD]
[TD]

1582,09​

[/TD]
[TD]

1624,81​

[/TD]
[TD]

1668,68​

[/TD]
[TD]

1713,73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Student 2​

[/TD]
[TD]

350​

[/TD]
[TD]

1,90%​

[/TD]
[TD]

2​

[/TD]
[TD]

350​

[/TD]
[TD]

356,65​

[/TD]
[TD]

363,43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Student 3​

[/TD]
[TD]

6800​

[/TD]
[TD]

3,10%​

[/TD]
[TD]

7​

[/TD]
[TD]

6800​

[/TD]
[TD]

7010,8​

[/TD]
[TD]

7228,13​

[/TD]
[TD]

7452,2​

[/TD]
[TD]

7683,22​

[/TD]
[TD]

7921,4​

[/TD]
[TD]

8166,96​

[/TD]
[TD]

8420,14​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Student 4​

[/TD]
[TD]

250​

[/TD]
[TD]

2,00%​

[/TD]
[TD]

3​

[/TD]
[TD]

250​

[/TD]
[TD]

255​

[/TD]
[TD]

260,1​

[/TD]
[TD]

265,3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in E2 copied down
=C2

Formula in F2 copied across and down
=IF($D2>=F$1,ROUND(E2*(1+$C2),2),"")

M.

 

[nidzela]

Sorry, i'm not understanding what you need - how and where to put the results (steps) for each student .

Hope someone else can help you.

M.

ouch, possibly my question sounds a bit confusing... i apologise. let me try to make it clearer: i didn't explain that i have 2 sheets in mind. let's say Sheet1 contains a table like (borrowing Marcelo's pattern):

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD="align: center"]c[/TD]
[TD="align: right"]250 (=$c)[/TD]
[TD="align: right"]5 (=B1*$i)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]i[/TD]
[TD="align: right"]255 (=B1+C1)[/TD]
[TD="align: right"]5.10 (=B2*$i)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]n[/TD]
[TD="align: right"]260.10 (=B2+C2)[/TD]
[TD="align: right"]5.20 (=B3*$i)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]265.30 (=B3+C3)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

in this example, n=3, so the answer to be extracted appears in B4 (one row more than years). this is also what my students are supposed to send via e-mail.

let's say also that student's data is in Sheet2:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]c
[/TD]
[TD]i
[/TD]
[TD]n
[/TD]
[TD]Result
[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Student 1
[/TD]
[TD]

250​

[/TD]
[TD]

2,50%​

[/TD]
[TD]

3​

[/TD]
[TD]

265.30​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]Student 2
[/TD]
[TD]

350​

[/TD]
[TD]

1,90%​

[/TD]
[TD]

2​

[/TD]
[TD]

363,43​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]Student 3
[/TD]
[TD]

6800​

[/TD]
[TD]

3,10%​

[/TD]
[TD]

7​

[/TD]
[TD]

8420,14​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]Student 4
[/TD]
[TD]

250​

[/TD]
[TD]

2,00%​

[/TD]
[TD]

3​

[/TD]
[TD]

265,30​

[/TD]
[/TR]
</tbody>[/TABLE]

i want to autofill column E somehow, but not using the formula =B2*(1+C2)^D2 (which is completely correct, but theorical... numerical answer by Excel may be different because of increasing error in iterative calculation) neither using VB (that would be something like: spinbutton from 1 to 29 students, read data on Sheet2, replace values on Sheet1, then extract and write down answer from row n+1). i'd like to get that by some combination of array formulas, scenarios, or whatever.

so it would be like performing Sheet1 as an emulator for every student's particular case.
does this make the question clearer?

 

[nidzela]

See if this helps


[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]

c​

[/TD]
[TD]

i​

[/TD]
[TD]

n​

[/TD]
[TD]

0​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

7​

[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

Student 1​

[/TD]
[TD]

1500​

[/TD]
[TD]

2,70%​

[/TD]
[TD]

5​

[/TD]
[TD]

1500​

[/TD]
[TD]

1540,5​

[/TD]
[TD]

1582,09​

[/TD]
[TD]

1624,81​

[/TD]
[TD]

1668,68​

[/TD]
[TD]

1713,73​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

Student 2​

[/TD]
[TD]

350​

[/TD]
[TD]

1,90%​

[/TD]
[TD]

2​

[/TD]
[TD]

350​

[/TD]
[TD]

356,65​

[/TD]
[TD]

363,43​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

Student 3​

[/TD]
[TD]

6800​

[/TD]
[TD]

3,10%​

[/TD]
[TD]

7​

[/TD]
[TD]

6800​

[/TD]
[TD]

7010,8​

[/TD]
[TD]

7228,13​

[/TD]
[TD]

7452,2​

[/TD]
[TD]

7683,22​

[/TD]
[TD]

7921,4​

[/TD]
[TD]

8166,96​

[/TD]
[TD]

8420,14​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

Student 4​

[/TD]
[TD]

250​

[/TD]
[TD]

2,00%​

[/TD]
[TD]

3​

[/TD]
[TD]

250​

[/TD]
[TD]

255​

[/TD]
[TD]

260,1​

[/TD]
[TD]

265,3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in E2 copied down
=C2

Formula in F2 copied across and down
=IF($D2>=F$1,ROUND(E2*(1+$C2),2),"")

M.

thanks for your suggestion, Marcelo!

at last i decided to use VB to create a couple of macros. the aim was not to spend so much colums for calculations, as long as the first sheet already contains the required information. i've used a Spinbutton and some code like:

For k = 3 To 5
Sheets(1).Cells(k, 3) = Cells(Spinbutton1 + 3, k) 'overwrites starting values
Next
Cells(Spinbutton1 + 3, 5) = Sheets(1).Cells(years + 3, 6) 'places found answer in the 5th column

my first approach seems difficult to achieve (maybe impossible in the terms i was asking for), and i've left it for some other day.

 

[Marcelo Branco]

You are welcome.

Sorry, for failing to understand exactly what you wanted and could not help more.

Anyway, thanks for the feedback and good luck in your project.

M.