add a range and subtract a range at same time

[jdmardell]

hi everyone this is stumping me so have this table below and trying to work out column H and G. Column H in in H3 i am trying to add H2 plus F2 + F3 minus B3 and get a result and then H4 i am trying to add F2+F3=F4 minus B3 and B4 and so on. so i have this formula for H3 =SUM(F$2:F3)-@B$3:B3 and it works for that cell but when i copy it down it does not work.



the second part is column G i am trying to work out running balance of rev per week as D column numbers are paid each week so for instance last week i got 13,125 from revenue in column A and no week 2 i get another 13,125 (A1) plus 70 from A2 and 70 from A3 to i should have a balance and then i take some of that revenue and buy another item in A4 which is 2,500 and get 80 per week so the balance should come down but the revenue goes up



appreciate your help

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1		price	My money contributed AUD	rev Per week	rev Weeks Left	total rev Expected	revBalance	rev Expect at End of 91 weeks	should be
2	1		230,000	13,125	91	1,194,375		1,194,375
3	2	2,248		70	91	6,370		1,198,497
4	3	2,249		70	91	6,370		1,204,866	1,202,618
5
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=SUM(D2*E2)
H2	H2	=SUM(F$2:F2)
H3:H4	H3	=@SUM(F$2:F3)-@B$3:B3

 

[Peter_SSs]

Welcome to the MrExcel board!

For the first part try this.

22 01 25.xlsm
	B	C	D	E	F	G	H
1	price	My money contributed AUD	rev Per week	rev Weeks Left	total rev Expected	revBalance	rev Expect at End of 91 weeks
2		230000	13125	91	1194375		1194375
3	2248		70	91	6370		1198497
4	2249		70	91	6370		1202618
rev Expect

Cell Formulas
Range	Formula
F2:F4	F2	=D2*E2
H2	H2	=F2
H3:H4	H3	=H2+F3-B3

The second part I did not understand. Could you re-do a set of sample data (perhaps include a couple more rows) and enter the expected results manually and explain how you got those results?

 

[jdmardell]

awesome thanks for the second part last week 16/01/2022 i had a balance of 1,000 (G2) and i bought A2 and that had 92 weeks remaining on revenue stream then week 2 is 23/01/2022 i bought A2 which cost 2,248 so as this is week 2 i received 13,125 plus my 1,000 balance so should be 11,877 then i bought same time A4 which cost 2249 so balance reduces to 9628 then the next week 30/1/2022 i buy A5 which costs 2250 so i would have the opening balance of 9,628 plus revenue of D2 13,125 + D3 70 + D4 so balance should be 20,643

Book1.xlsx
	A	B	C	D	E	F	G	H	I
1		price	My money contributed AUD	rev Per week	rev Weeks Left	total rev Expected	revBalance	rev Expect at End of 91 weeks	should be
2	1		230,000	13,125	92	1,207,500	1,000	1,207,500
3	2	2,248		70	91	6,370	11,877	1,211,622
4	3	2,249		70	91	6,370	9,628	1,215,743	1,202,618
5	4	2250		70	90	6,300	20,643	1,219,793	1,206,738
6
7
8	start date	16/01/2022
9	todays date	23/01/2022
10	next weeks date	30/01/2022
11
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=SUM(D2*E2)
H2	H2	=SUM(F$2:F2)
H3:H5	H3	=H2+F3-B3

 

[Peter_SSs]

That does not make sense to me at all I'm afraid.

First, this seems odd. How can you buy A2 twice?

and i bought A2 and that had 92 weeks remaining on revenue stream then week 2 is 23/01/2022 i bought A2

Also, what do you mean by "I bought A2"?
Do you mean in row 2 you bought 1 thing, in row 3 your bough 2 things, in row 4 you bought 3 things etc?

Thirdly, some rows you seem to be taking account of what is in column D and some you are not.

Next, your descriptions refer to dates, but none of rows 1 to 5 contain dates so how do I know which row you are referring to?

 

[jdmardell]

sorry i bought A3 in second week, second transaction in second week was 2248 and third transaction was second week as well of 2249. The weeks left is a variable number not based on date so i have to manually enter that number

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Date	price	My money contributed AUD	rev Per week	rev Weeks Left	total rev Expected	revBalance	rev Expect at End of 91 weeks	should be
2	1	16/01/2022		230,000	13,125	92	1,207,500	1,000	1,207,500
3	2	23/01/2022	2,248		70	91	6,370	11,877	1,211,622
4	3	23/01/2022	2,249		70	91	6,370	9,628	1,215,743	1,202,618
5	4	30/01/2022	2250		70	90	6,300	20,643	1,219,793	1,206,738
6
7
8	start date		16/01/2022
9	todays date		23/01/2022
10	next weeks date		30/01/2022
Sheet1

Cell Formulas
Range	Formula
G2:G5	G2	=SUM(E2*F2)
I2	I2	=SUM(G$2:G2)
I3:I5	I3	=I2+G3-C3

 

[Peter_SSs]

Sorry, I am lost.

 

[jdmardell]

so i am trying to get a running balance weekly based on the rev per week and the variable is if i use some of that balance to buy something new which then takes that off the balance

 

[jdmardell]

anyone else have any thoughts?