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

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

Do you think state sales tax should be charged in the United States on all Internet sales?

Discuss in The Lounge

Poll History