add a range and subtract a range at same time

jdmardell

New Member
Joined
Jan 24, 2022
Messages
5
Office Version
  1. 365
  2. 2021
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
ABCDEFGHI
1priceMy money contributed AUDrev Per weekrev Weeks Lefttotal rev ExpectedrevBalancerev Expect at End of 91 weeksshould be
21230,00013,125911,194,3751,194,375
322,24870916,3701,198,497
432,24970916,3701,204,8661,202,618
5
6
7
8
9
10
11
Sheet1
Cell Formulas
RangeFormula
F2:F4F2=SUM(D2*E2)
H2H2=SUM(F$2:F2)
H3:H4H3=@SUM(F$2:F3)-@B$3:B3
 

Attachments

  • Untitled.jpg
    Untitled.jpg
    46.8 KB · Views: 5

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,461
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

For the first part try this.

22 01 25.xlsm
BCDEFGH
1priceMy money contributed AUDrev Per weekrev Weeks Lefttotal rev ExpectedrevBalancerev Expect at End of 91 weeks
2230000131259111943751194375
32248709163701198497
42249709163701202618
rev Expect
Cell Formulas
RangeFormula
F2:F4F2=D2*E2
H2H2=F2
H3:H4H3=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

New Member
Joined
Jan 24, 2022
Messages
5
Office Version
  1. 365
  2. 2021
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
ABCDEFGHI
1priceMy money contributed AUDrev Per weekrev Weeks Lefttotal rev ExpectedrevBalancerev Expect at End of 91 weeksshould be
21230,00013,125921,207,5001,0001,207,500
322,24870916,37011,8771,211,622
432,24970916,3709,6281,215,7431,202,618
54225070906,30020,6431,219,7931,206,738
6
7
8start date16/01/2022
9todays date23/01/2022
10next weeks date30/01/2022
11
Sheet1
Cell Formulas
RangeFormula
F2:F5F2=SUM(D2*E2)
H2H2=SUM(F$2:F2)
H3:H5H3=H2+F3-B3
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
56,461
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 24, 2022
Messages
5
Office Version
  1. 365
  2. 2021
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
ABCDEFGHIJ
1DatepriceMy money contributed AUDrev Per weekrev Weeks Lefttotal rev ExpectedrevBalancerev Expect at End of 91 weeksshould be
2116/01/2022230,00013,125921,207,5001,0001,207,500
3223/01/20222,24870916,37011,8771,211,622
4323/01/20222,24970916,3709,6281,215,7431,202,618
5430/01/2022225070906,30020,6431,219,7931,206,738
6
7
8start date16/01/2022
9todays date23/01/2022
10next weeks date30/01/2022
Sheet1
Cell Formulas
RangeFormula
G2:G5G2=SUM(E2*F2)
I2I2=SUM(G$2:G2)
I3:I5I3=I2+G3-C3
 
Last edited by a moderator:

jdmardell

New Member
Joined
Jan 24, 2022
Messages
5
Office Version
  1. 365
  2. 2021
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
 

Forum statistics

Threads
1,175,457
Messages
5,897,531
Members
434,660
Latest member
Stoyf

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
Top