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) = $9

Calculate 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.


See More: excel if then condition, use remainder

Report •

#1
December 8, 2017 at 15:55:04
re: Day 3 - $110 * (.09) = $9.90 + $8 (remainder from previous calculation) = $19.80

How 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
✔ Best Answer
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   	

	

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

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 issues

message edited by niteyez


Report •

#5
December 10, 2017 at 06:39:07
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


Report •

Ask Question