Subtract value over multiple cells

B

Blake0920

Board Regular
Joined
Jan 2, 2022
Messages
60
Office Version
  1. 2016
Platform
  1. Windows
Trying to figure out a formula where I can subtract a value from a series of cells.

This is what I have now but it is not putting that value in any of the cells. =SUM(((Y12:Y18*(AD5:AD5="8")*(AA5:AA5>="02-01-2022")*(AA5:AA5<="02-31-2022"))))-AE5
 

Attachments

  • Screenshot 2022-02-21 225933.png
    Screenshot 2022-02-21 225933.png
    33.4 KB · Views: 24

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
It is not clear to me (& I assume the other 20+ people who have viewed you question) exactly what you are trying to do.

Consider XL2BB for sample data and expected results so helpers can easily copy from it to test.
Explain again in relation to the new sample data and expected results.
 
Upvote 0
I simply want to subtract the -107 from multiple cells at once.

The 107 is derived from a customer that stops paying in Feb-2022. I need the $107 to subtract from the next 8 months because that money will no longer be coming in.

I have tried to install this XL2BB multiples times but cannot get it to work
 
Upvote 0
Sales Orders 2022-2.0 - Blake.xlsm
WXYZAAABACADAEAFAG
3
4mo paymentName Cancel DateOG Mo PPOwing DurationMo. Total
5Blake Taylor02-01-202212$863.338$107.921
63
76
89
912
10
11
12feb$13,634.63mar-$107.92
13mar$26,746.63apr
14apr$23,885.25may
15may$23,012.83jun
16jun$19,913.46jul
17jul$17,578.51aug
18aug$15,304.88sep
19sep$13,483.53oct
20oct$11,435.90nov
21nov$9,282.88dec
22dec$5,492.23jan
23jan$815.70feb
24feb mar
25
26
Projection
Cell Formulas
RangeFormula
AA12AA12=SUM(((Y12:Y18*(AD5:AD5="8")*(AA5:AA5>="02-01-2022")*(AA5:AA5<="02-31-2022"))))-AE5
Y12Y12=(O5/U5)/X9*(U5)+(O8/U8)/X9*(U8)+(O11/U11)/X9*(U11)+(O14/U14)/X9*(U14)+(O17/U17)/X9*(U17)
Y13Y13=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)+(O8/12)+(O11/12)+(O14/12)+(O17/12)+(O20/12)
Y14Y14=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)+(O11/12)+(O14/12)+(O17/12)+(O20/12)
Y15Y15=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)+(O14/12)+(O17/12)+(O20/12)
Y16Y16=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)+(O17/12)+(O20/12)
Y17Y17=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)+(O20/12)
Y18Y18=(O38/X9)+(O35/12)+(O23/12)+(O26/12)+(O29/12)+(O32/12)
Y19Y19=(O38/X9)+(O35/12)+(O26/12)+(O29/12)+(O32/12)
Y20Y20=(O38/X9)+(O35/12)+(O29/12)+(O32/12)
Y21Y21=(O38/X9)+(O35/12)+(O32/12)
Y22Y22=(O38/X9)+(O35/12)
Y23Y23=(O38/X9)
Y24Y24=(O41/X9)
 
Upvote 0
Here is the table that I have been creating to give you a better perspective

Sales Orders 2022-2.0 - Blake.xlsm
ABCDEFGHI
4MonthTotalPay-In-FullPP TotalTotal PP12mo pay9mo pay6mo pay3mo pay
5Feb-22$59,280.25$17,644.43$41,635.82192$31,213.13$2,386.30$3,995.42$4,040.97
6Mar-22$35,468.68 $35,468.68169$26,746.63$2,386.30$2,294.78$4,040.97
7Apr-22$29,220.31 $29,220.31147$23,885.25$2,386.30$1,084.99$1,863.78
8May-22$25,696.59 $25,696.59137$23,012.83$1,598.78$1,084.99 
9Jun-22$22,358.89 $22,358.89120$19,913.46$1,360.44$1,084.99 
10Jul-22$18,773.23 $18,773.23103$17,578.51$853.89$340.83 
11Aug-22$15,548.77 $15,548.7790$15,304.88$243.89  
12Sep-22$13,727.42 $13,727.4278$13,483.53$243.89  
13Oct-22$11,435.90 $11,435.9067$11,435.90   
14Nov-22$9,282.88 $9,282.8854$9,282.88   
15Dec-22$5,492.23 $5,492.2334$5,492.23   
16Jan-23$815.70$815.704$815.70   
17Feb-23      
18Mar-23      
19Apr-23      
20May-23      
21Jun-23      
22Jul-23      
23Aug-23      
24Sep-23      
25Oct-23      
26Nov-23      
27Dec-23     
28Total$247,100.85$229,456.42
Projection
Cell Formulas
RangeFormula
G5G5=N38/X8+(N35/9)+(N23/9)+(N26/9)+(N29/9)+(N20/9)
H5H5=M38/X7+(M35/6)+(M23/6)+(M26/6)
I5I5=L38/X6+(L35/3)
G6G6=IF(COUNTA($G$5:G5)+1<=$X8,$N38/$X8,0)+(N41/9)+(N35/9)+(N23/9)+(N26/9)+(N29/9)+(N20/9)
H6H6=IF(COUNTA($H$5:H5)+1<=$X7,$M38/$X7,0)+(M41/6)+(M35/6)+(M26/6)
I6I6=IF(COUNTA($I$5:I5)+1<=$X6,$L38/$X6,0)+(L41/3)+(L35/3)
G7G7=IF(COUNTA($G$5:G6)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N35/9)+(N23/9)+(N26/9)+(N29/9)+(N20/9)
H7H7=IF(COUNTA($H$5:H6)+1<=$X7,$M38/$X7,0)+(M41/6)+(M44/6)+(M35/6)
I7I7=IF(COUNTA($I$5:I6)+1<=$X6,$L38/$X6,0)+(L41/3)+(L44/3)
G8G8=IF(COUNTA($G$5:G7)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N35/9)+(N23/9)+(N26/9)+(N29/9)
H8H8=IF(COUNTA($H$5:H7)+1<=$X7,$M38/$X7,0)+(M41/6)+(M44/6)+(M47/6)+(M35/6)
I8I8=IF(COUNTA($I$5:I7)+1<=$X6,$L38/$X6,0)+(L41/3)+(L44/3)+(L47/3)
G9G9=IF(COUNTA($G$5:G8)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N35/9)+(N26/9)+(N29/9)
H9H9=IF(COUNTA($H$5:H8)+1<=$X7,$M38/$X7,0)+(M41/6)+(M44/6)+(M47/6)+(M50/6)+(M35/6)
I9I9=IF(COUNTA($I$5:I8)+1<=$X6,$L38/$X6,0)+(L44/3)+(L47/3)+(L50/3)
G10G10=IF(COUNTA($G$5:G9)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N35/9)+(N29/9)
H10H10=IF(COUNTA($H$5:H9)+1<=$X7,$M38/$X7,0)+(M41/6)+(M44/6)+(M47/6)+(M50/6)+(M53/6)
I10I10=IF(COUNTA($I$5:I9)+1<=$X6,$L38/$X6,0)+(L47/3)+(L50/3)+(L53/3)
G11G11=IF(COUNTA($G$5:G10)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N35/9)
H11H11=IF(COUNTA($H$5:H10)+1<=$X7,$M38/$X7,0)+(M41/6)+(M44/6)+(M47/6)+(M50/6)+(M53/6)+(M56/6)
I11I11=IF(COUNTA($I$5:I10)+1<=$X6,$L38/$X6,0)+(L50/3)+(L53/3)+(L56/3)
G12G12=IF(COUNTA($G$5:G11)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N35/9)
H12H12=IF(COUNTA($H$5:H11)+1<=$X7,$M38/$X7,0)+(M44/6)+(M47/6)+(M50/6)+(M53/6)+(M56/6)+(M59/6)
I12I12=IF(COUNTA($I$5:I11)+1<=$X6,$L38/$X6,0)+(L53/3)+(L56/3)+(L59/3)
G13G13=IF(COUNTA($G$5:G12)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N62/9)
H13H13=IF(COUNTA($H$5:H12)+1<=$X7,$M38/$X7,0)+(M47/6)+(M50/6)+(M53/6)+(M56/6)+(M59/6)+(M62/6)
I13I13=IF(COUNTA($I$5:I12)+1<=$X6,$L38/$X6,0)+(L56/3)+(L59/3)+(L62/3)
G14G14=IF(COUNTA($G$5:G13)+1<=$X8,$N38/$X8,0)+(N41/9)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N62/9)+(N65/9)
H14H14=IF(COUNTA($H$5:H12)+1<=$X7,$M38/$X7,0)+(M50/6)+(M53/6)+(M56/6)+(M59/6)+(M62/6)+(M65/6)
I14I14=IF(COUNTA($I$5:I13)+1<=$X6,$L38/$X6,0)+(L59/3)+(L62/3)+(L65/3)
G15G15=IF(COUNTA($G$5:G14)+1<=$X8,$N38/$X8,0)+(N44/9)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H15H15=IF(COUNTA($H$5:H14)+1<=$X7,$M38/$X7,0)+(M53/6)+(M56/6)+(M59/6)+(M62/6)+(M65/6)+(M68/6)
I15I15=IF(COUNTA($I$5:I14)+1<=$X6,$L38/$X6,0)+(L62/3)+(L65/3)+(L68/3)
G16G16=IF(COUNTA($G$5:G15)+1<=$X8,$N38/$X8,0)+(N47/9)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H16H16=IF(COUNTA($H$5:H15)+1<=$X7,$M38/$X7,0)+(M56/6)+(M59/6)+(M62/6)+(M65/6)+(M68/6)
I16I16=IF(COUNTA($I$5:I15)+1<=$X6,$L38/$X6,0)+(L65/3)+(L68/3)
G17G17=IF(COUNTA($G$5:G16)+1<=$X8,$N38/$X8,0)+(N50/9)+(N53/9)+(N56/9)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H17H17=IF(COUNTA($H$5:H16)+1<=$X7,$M38/$X7,0)+(M59/6)+(M62/6)+(M65/6)+(M68/6)
I17I17=IF(COUNTA($I$5:I16)+1<=$X6,$L38/$X6,0)+(L68/3)
G18G18=IF(COUNTA($G$5:G17)+1<=$X8,$N38/$X8,0)+(N53/9)+(N56/9)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H18H18=IF(COUNTA($H$5:H17)+1<=$X7,$M38/$X7,0)+(M62/6)+(M65/6)+(M68/6)
I18I18=IF(COUNTA($I$5:I17)+1<=$X6,$L38/$X6,0)
G19G19=IF(COUNTA($G$5:G18)+1<=$X8,$N38/$X8,0)+(N56/9)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H19H19=IF(COUNTA($H$5:H18)+1<=$X7,$M38/$X7,0)+(M65/6)+(M68/6)
I19I19=IF(COUNTA($I$5:I18)+1<=$X6,$L38/$X6,0)
G20G20=IF(COUNTA($G$5:G19)+1<=$X8,$N38/$X8,0)+(N59/9)+(N62/9)+(N65/9)+(N68/9)
H20H20=IF(COUNTA($H$5:H19)+1<=$X7,$M38/$X7,0)+(M68/6)
I20I20=IF(COUNTA($I$5:I19)+1<=$X6,$L38/$X6,0)
G21G21=IF(COUNTA($G$5:G20)+1<=$X8,$N38/$X8,0)+(N62/9)+(N65/9)+(N68/9)
H21H21=IF(COUNTA($H$5:H20)+1<=$X7,$M38/$X7,0)
I21I21=IF(COUNTA($I$5:I20)+1<=$X6,$L38/$X6,0)
G22G22=IF(COUNTA($G$5:G21)+1<=$X8,$N38/$X8,0)+(N65/9)+(N68/9)
H22H22=IF(COUNTA($H$5:H21)+1<=$X7,$M38/$X7,0)
I22I22=IF(COUNTA($I$5:I21)+1<=$X6,$L38/$X6,0)
G23G23=IF(COUNTA($G$5:G22)+1<=$X8,$N38/$X8,0)+(N68/9)
H23H23=IF(COUNTA($H$5:H22)+1<=$X7,$M38/$X7,0)
I23I23=IF(COUNTA($I$5:I22)+1<=$X6,$L38/$X6,0)
G24G24=IF(COUNTA($G$5:G23)+1<=$X8,$N38/$X8,0)
H24H24=IF(COUNTA($H$5:H23)+1<=$X7,$M38/$X7,0)
I24I24=IF(COUNTA($I$5:I23)+1<=$X6,$L38/$X6,0)
G25G25=IF(COUNTA($G$5:G24)+1<=$X8,$N38/$X8,0)
H25H25=IF(COUNTA($H$5:H24)+1<=$X7,$M38/$X7,0)
I25I25=IF(COUNTA($I$5:I24)+1<=$X6,$L38/$X6,0)
G26G26=IF(COUNTA($G$5:G25)+1<=$X8,$N38/$X8,0)
H26H26=IF(COUNTA($H$5:H25)+1<=$X7,$M38/$X7,0)
I26I26=IF(COUNTA($I$5:I25)+1<=$X6,$L38/$X6,0)
G27G27=IF(COUNTA($G$5:G26)+1<=$X8,$N38/$X8,0)
H27H27=IF(COUNTA($H$5:H26)+1<=$X7,$M38/$X7,0)
I27I27=IF(COUNTA($I$5:I26)+1<=$X6,$L38/$X6,0)
C5C5=SUMPRODUCT(((Orders!A4:A70>="02-01-2022")*(Orders!A4:A70<="02-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C6C6=SUMPRODUCT(((Orders!A4:A70>="03-01-2022")*(Orders!A4:A70<="03-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C7C7=SUMPRODUCT(((Orders!A4:A70>="04-01-2022")*(Orders!A4:A70<="04-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C8C8=SUMPRODUCT(((Orders!A4:A70>="05-01-2022")*(Orders!A4:A70<="05-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C9C9=SUMPRODUCT(((Orders!A4:A70>="06-01-2022")*(Orders!A4:A70<="06-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C10C10=SUMPRODUCT(((Orders!A4:A70>="07-01-2022")*(Orders!A4:A70<="07-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C11C11=SUMPRODUCT(((Orders!A4:A70>="08-01-2022")*(Orders!A4:A70<="08-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C12C12=SUMPRODUCT(((Orders!A4:A70>="09-01-2022")*(Orders!A4:A70<="09-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C13C13=SUMPRODUCT(((Orders!A4:A70>="10-01-2022")*(Orders!A4:A70<="10-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C14C14=SUMPRODUCT(((Orders!A4:A70>="11-01-2022")*(Orders!A4:A70<="11-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
C15C15=SUMPRODUCT(((Orders!A4:A70>="12-01-2022")*(Orders!A4:A70<="12-31-2022"))*(Orders!H4:H70)*(Orders!I4:I70="In full"))
F5:F16F5=Y12
E5E5=SUM(R35+S35+T35+U35)+(R38+S38+T38+U38)+(U5)+(U8)+(U11)+(U14)+(U17)+(U20)+(T20)+(U23)+(T23)+(S23)+(U26)+(T26)+(S26)+(U29)+(T29)+(U32)
E6E6=SUM(R35+S35+T35+U35)+(R38+S38+T38+U38)+(R41+S41+T41+U41)+(U8)+(U11)+(U14)+(U17)+(U20)+(T20)+(U23)+(T23)+(U26)+(T26)+(S26)+(U29)+(T29)+(U32)
E7E7=SUM(S35+T35+U35)+(R38+S38+T38+U38)+(R41+S41+T41+U41)+(R44+S44+T44+U44)+(U11)+(U14)+(U17)+(U20)+(T20)+(U23)+(T23)+(U26)+(T26)+(U29)+(T29)+(U32)
E8E8=SUM(S35+T35+U35)+(S38+T38+U38)+(R41+S41+T41+U41)+(R44+S44+T44+U44)+(R47+S47+T47+U47)+(U14)+(U17)+(U20)+(U23)+(T23)+(U26)+(T26)+(U29)+(T29)+(U32)
E9E9=SUM(S35+T35+U35)+(S38+T38+U38)+(S41+T41+U41)+(R44+S44+T44+U44)+(R47+S47+T47+U47)+(R50+S50+T50+U50)+(U17)+(U20)+(U23)+(U26)+(T26)+(U29)+(T29)+(U32)
E10E10=SUM(T35+U35)+(S38+T38+U38)+(S41+T41+U41)+(S44+T44+U44)+(R47+S47+T47+U47)+(R50+S50+T50+U50)+(R53+S53+T53+U53)+(U20)+(U23)+(U26)+(U29)+(T29)+(U32)
E11E11=SUM(T35+U35)+(T38+U38)+(S41+T41+U41)+(S44+T44+U44)+(S47+T47+U47)+(R50+S50+T50+U50)+(R53+S53+T53+U53)+(R56+S56+T56+U56)+(U23)+(U26)+(U29)+(U32)
E12E12=SUM(T35+U35)+(T38+U38)+(T41+U41)+(S44+T44+U44)+(S47+T47+U47)+(S50+T50+U50)+(R53+S53+T53+U53)+(R56+S56+T56+U56)+(R59+S59+T59+U59)+(U26)+(U29)+(U32)
E13E13=SUM(U35)+(T38+U38)+(T41+U41)+(T44+U44)+(S47+T47+U47)+(S50+T50+U50)+(S53+T53+U53)+(R56+S56+T56+U56)+(R59+S59+T59+U59)+(R62+S62+T62+U62)+(U29)+(U32)
E14E14=SUM(U35)+(U38)+(T41+U41)+(T44+U44)+(T47+U47)+(S50+T50+U50)+(S53+T53+U53)+(S56+T56+U56)+(R59+S59+T59+U59)+(R62+S62+T62+U62)+(R65+S65+T65+U65)+(U32)
E15E15=SUM(U35)+(U38)+(U41)+(T44+U44)+(T47+U47)+(T50+U50)+(S53+T53+U53)+(S56+T56+U56)+(S59+T59+U59)+(R62+S62+T62+U62)+(R65+S65+T65+U65)+(R68+S68+T68+U68)
E16E16=SUM(U38)+(U41)+(U44)+(T47+U47)+(T50+U50)+(T53+U53)+(S56+T56+U56)+(S59+T59+U59)+(S62+T62+U62)+(R65+S65+T65+U65)+(R68+S68+T68+U68)
E17E17=SUM(U41)+(U44)+(U47)+(T50+U50)+(T53+U53)+(T56+U56)+(S59+T59+U59)+(S62+T62+U62)+(S65+T65+U65)+(R68+S68+T68+U68)
E18E18=SUM(U44)+(U47)+(U50)+(T53+U53)+(T56+U56)+(T59+U59)+(S62+T62+U62)+(S65+T65+U65)+(S68+T68+U68)
E19E19=SUM(U47)+(U50)+(U53)+(T56+U56)+(T59+U59)+(T62+U62)+(S65+T65+U65)+(S68+T68+U68)
E20E20=SUM(U50)+(U53)+(U56)+(T59+U59)+(T65+U65)+(S68+T68+U68)
E21E21=SUM(U53)+(U56)+(U59)+(T62+U62)+(T65+U65)+(T68+U68)
E22E22=SUM(U56)+(U59)+(U62)+(T65+U65)+(T68+U68)
E23E23=SUM(U59)+(U62)+(U65)+(T68+U68)
E24E24=SUM(U62)+(U65)+(U68)
E25E25=SUM(U65)+(U68)
E26E26=SUM(U68)
B5:B27B5=SUMPRODUCT(C5+D5)
B28,D28B28=AGGREGATE(9,5,B5:B27)
D5:D27D5=AGGREGATE(9,5,F5:I5)
 
Upvote 0
I have modified the formula in P12

=((Q5>="02-01-2022")*(Q5<="02-31-2022")*U5)

Now I just need this formula to replicate itself 8 rows down (based on the value of T5

Sales Orders 2022-2.1 - Blake.xlsm
LMNOPQRSTUVW
3
4mo paymentName Cancel DateOG Mo PPOwing DurationMo. Total
502-15-202212$863.338$107.921
63
76
89
912
10
11Current Owing Reduced
12feb$31,492.43mar$107.92$31,384.52
13mar$27,025.93apr$27,025.93
14apr$24,164.54may$24,164.54
15may$23,292.13jun$23,292.13
16jun$20,192.76jul$20,192.76
17jul$17,857.81aug$17,857.81
18aug$15,584.18sep$15,584.18
19sep$13,762.83oct$13,762.83
20oct$11,715.20nov$11,715.20
21nov$9,562.18dec$9,562.18
22dec$5,771.53jan$5,771.53
23jan$1,095.00feb$1,095.00
24feb mar 
25
26
PP Mo
Cell Formulas
RangeFormula
U5U5=S5/T5
P12P12=((Q5>="02-01-2022")*(Q5<="02-31-2022")*U5)
N12N12=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
N13N13=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
N14N14=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
N15N15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
N16N16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
N17N17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
N18N18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
N19N19=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
N20N20=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
N21N21=(D38/M9)+(D35/12)+(D32/12)
N22N22=(D38/M9)+(D35/12)
N23N23=(D38/M9)
N24N24=(D41/M9)
R12:R23R12=N12-P12
R24R24=N24+P24
 
Upvote 0
please close this too confusing to other members. Will repost better worded one.
 
Upvote 0
Please do not start a new thread, for this question. as it is against board rules. Per Forum Rules (#12)
 
Upvote 0
Okay i just wanted to clarify any confusion as my question has been looked at dozens of times
 
Upvote 0
I need to replicate U5 eight(8) times beginning in P12:P18.

Essentially I have a payment plan and some customers have stopped paying. I need to project how much less that will be each month based on how many months they have remaining.

In other words is there a way I can have $107.92 appear in the range of cells without have to input in manually?

Sales Orders 2022-2.1 - Blake.xlsm
MNOPQRSTUV
4mo paymentName Cancel DateOG Mo PPOwing DurationMo. Total
502-15-202212$863.338$107.921
6302-16-20226573.213$191.071
76
89
912
10
11Current Owing Reduced
12feb$31,492.43mar$107.92$31,384.52
13mar$27,025.93apr$27,025.93
14apr$24,164.54may$24,164.54
15may$23,292.13jun$23,292.13
16jun$20,192.76jul$20,192.76
17jul$17,857.81aug$17,857.81
18aug$15,584.18sep$15,584.18
19sep$13,762.83oct$13,762.83
20oct$11,715.20nov$11,715.20
21nov$9,562.18dec$9,562.18
22dec$5,771.53jan$5,771.53
23jan$1,095.00feb$1,095.00
24feb mar 
PP Mo
Cell Formulas
RangeFormula
U5:U6U5=S5/T5
P12P12=SUM((Q5>="02-01-2022")*(Q5<="02-31-2022")*U5)
N12N12=(D5/J5)/M9*(J5)+(D8/J8)/M9*(J8)+(D11/J11)/M9*(J11)+(D14/J14)/M9*(J14)+(D17/J17)/M9*(J17)+(D20/J20)/M9*(J20)+(D23/J23)/M9*(J23)+(D26/J26)/M9*(J26)+(D29/J29)/M9*(J29)+(D32/J32)/M9*(J32)+(D35/J35)/M9*(J35)+(D38/J38)/M9*(J38)
N13N13=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D8/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
N14N14=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D11/12)+(D14/12)+(D17/12)+(D20/12)
N15N15=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D14/12)+(D17/12)+(D20/12)
N16N16=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D17/12)+(D20/12)
N17N17=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)+(D20/12)
N18N18=(D38/M9)+(D35/12)+(D23/12)+(D26/12)+(D29/12)+(D32/12)
N19N19=(D38/M9)+(D35/12)+(D26/12)+(D29/12)+(D32/12)
N20N20=(D38/M9)+(D35/12)+(D29/12)+(D32/12)
N21N21=(D38/M9)+(D35/12)+(D32/12)
N22N22=(D38/M9)+(D35/12)
N23N23=(D38/M9)
N24N24=(D41/M9)
R12:R23R12=N12-P12
R24R24=N24+P24

 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Auto update the date range
Replies
3
Views
248
kvsrinivasamurthy
K
V
  • Solved
Average Function
Replies
8
Views
395
Peter_SSs
Peter_SSs
G
  • Question Question
Vstack a variable range of 1D vertical arrays
Replies
3
Views
443
dreid1011
dreid1011
K
  • Question Question
VBA codes to replace all cells containing a certain value
Replies
2
Views
429
Snakehips
Snakehips
MrDB4Excel
  • Solved
Get the Correct Month
2
Replies
10
Views
173
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,829
Messages
6,181,222
Members
453,024
Latest member
Wingit77

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top