CODEWITHSUNDEEP
excelalgorithm
Gary Hullah
Gary Hullah

Reputation: 11

Converting for loop to Excel

I am trying to make the equivalent to a for loop as an Excel usable entry. I have a little experience coding, but none with Excel beyond the basic functions.

Scenario: Looking for the total cost to buy N items.
Each item cost a fixed rate more than the previous item (15% for now). I can get the cost of item N. I want the cell to display the total cost to get all items up to and including N.

N = Total Number of items in list, this changes based on calculations elsewhere on the spreadsheet.

Rate = 15% or 0.15, but can change, so I don't want it hard coded.

This formula gives me the cost of Item N._____ ((1/(1+Rate))*(((1+rate)^(N)))

I'm thinking something like the following would work, but I don't know how to write this is a way Excel can use.

Total = 0;
for(i=1,i=N,i++){
  Total = Total+((1/(1+Rate))*(((1+rate)^(i)));
};

This chart shows a manual calculation for what I want.

Item → Total Cost for N items

  1. → 1.00
  2. → 2.15
  3. → 3.47
  4. → 4.99
  5. → 6.74
  6. → 8.75
  7. → 11.07
  8. → 13.73
  9. → 16.79
  10. → 20.30

Thank you in advance for any help that you can provide.

Gary

Upvotes: 0

Views: 57

Answers (2)

ian0411
ian0411

Reputation: 4275

This is what I came up with even though it is a little bit similar to Dirk Reichel's great answer.

=SUMPRODUCT((1/(1+$B$2))*((1+$B$2)^ROW($A$1:A1)))

Basically I used SUMPRODUCT to sum up the total to the corresponding row.

Upvotes: 0

Dirk Reichel
Dirk Reichel

Reputation: 7979

If A1 is "Rate" and A2 is "N" then:

=SUMPRODUCT((1/(1+A1))*((1+A1)^ROW(A1:INDEX(A:A,A2))))

enter image description here

Upvotes: 3

Related Questions