# Solved excel if then condition, use remainder

Microsoft Excel 2010 - complete product...
December 8, 2017 at 12:36:07
Specs: Windows 10
 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 amountDay 3 - \$110 * (.09) = \$9.90 + \$8 (remainder from previous calculation) = \$19.80Calculate 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.

See More: excel if then condition, use remainder

#1
December 8, 2017 at 15:55:04
 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

Report •

#2
December 8, 2017 at 20:29:08
 @DerbyDad03 Typo. You are correct, the calc should have been \$17.90.

Report •

#3
December 9, 2017 at 17:20:04
 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.70 ```If 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 ```message edited by DerbyDad03

Report •

Related Solutions

#4
December 9, 2017 at 21:31:26
 @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 C``1 Initial Investment \$5,010.00``2 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 issuesmessage edited by niteyez

Report •

#5
December 10, 2017 at 06:39:07