Microsoft Excel 2010 - complete product...

Original Question: Hi. I'm trying to calculate a cell to check the sum of several cells, and if that number is greater than 500, to put the remainder in the cell.

e.g., if A3-A8-A12 > 500, put the amount over 500 in A1 (so if A3-A8-A12 = 700, A1 would be 200)Solution proposed for the above problem: =IF(SUM(A3-A8-A12)>500,SUM(A3-A8-A12)-500,"")

I have a similar issue that has a couple more steps. In this problem I need to sum cells and when they add to = or > 10 I need to add that increment of 10 to a value in another cell, then subtract that value from the current cell and take the remainder and add it to an accumulated leftover.

Ex: Day 1 - $100 * Interest amount (.09) = $9

Day 2 - $100 * (.09) = $9Calculate Sum of Day 1 + Day 2 Interest amount = $18 ($18 is > $10, so Add $10 to Day 3 amount making value $110 and take remainder of $8 and add it to Day 3 Interest amount

Day 3 - $110 * (.09) = $9.90 + $8 (remainder from previous calculation) = $19.80

Calculate Remainder of Day 1 and Day 2 ($8) + Day 3 Interest amount ($9.90) = $19.80 ($19.80 is > $10, so Add $10 to Day 4 amount making value $120 and take remainder of $9.80 and add it to Day 4 Interest amount... etc.

Day 4 - $120 * (.09) = $10.80 + $9.80 (remainder from previous calculation) = $20.60 ($20.80 is > 2 increments of 10, so add $20 to Day 5 amount making value $140 and take remainder of $0.80 and add it to Day 5 Interest amount... etc.

Help would be much appreciated.

re: Day 3 - $110 * (.09) = $9.90 + $8 (remainder from previous calculation) = $19.80How does $9.90 + $8 = $19.80?

I tried 3 different calculators and Excel 2013. I get $17.90 every time.

message edited by DerbyDad03

@DerbyDad03 Typo. You are correct, the calc should have been $17.90.

Well, that throws off all of your calculations. Day 5 no longer has to deal with a value greater than 20, since the value for that day $18.70. That changes a lot of the values that follow. So... Assuming these values are correct, the formulas to get them are shown below:

A B C D 1 Daily Value Daily Interest Remainder Sum Interest Rate 2 100 9.00 9% 3 100 9.00 18.00 4 110 9.90 17.90 5 120 10.80 18.70 6 130 11.70 20.40 7 150 13.50 13.90 8 160 14.40 18.30 9 170 15.30 23.60 10 190 17.10 20.70If that's what you want, try these formulas. Let us know how it works out.

A B C D 1 Daily Value Daily Interest Remainder Sum Interest Rate 2 100 =A2*$D$2 9% 3 =A2 =A3*$D$2 =SUM(B2+B3) 4 =SUM(A3,ROUNDDOWN(C3,-1)) =A4*$D$2 =SUM(B4,MOD(C3,10)) 5 ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ Drag A4:C4 Down ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ ▼ 6

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

@DerbyDad03 Thanks so much. You included everything I needed to make it work. I had to tweak a couple things to get what I needed, but that was a perfect roadmap! ROUNDDOWN and MOD made it work. Once I got those figured out it was gravy. A C1 Initial Investment $5,010.002 Average Daily Interest Rate (Paid on Init + Reinvest) 0.95%3 Daily Interest Bonus(Paid on $5,010 ONLY) 0.20%A B C D E Days Investment Daily Int Int plus Remainder Daily Reinvest 6 1 $5,010.00 $57.62 $57.62 $50.00 7 2 $5,060.00 $58.09 $65.71 $60.00

A B C D E Days Investment Daily Int Int + Remdr Daily Reinvest 6 1 =$C$1 =($B$6*$C$2)+($B$6*$C$3) =C6 =FLOOR(D6,10) 7 2 =SUM(B6,ROUNDDOWN(C6,-1)) =(($B$6*$C$2)+($B$6*$C$3))+((B7-$B$6)*$C$2) =SUM(MOD(C6,10),C7) =FLOOR(D7,10)P.S. sorry for the spacing issues

message edited by niteyez

I'm glad I could get your started down the right path. The fact that you included a detailed description of your requirements helped me help you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Would you use Amazon to buy airline tickets?

Discuss in The Lounge

Poll History