Sum until value reach within dates

Ellmal

New Member
Joined
Feb 24, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi everyone,

It is my first time posting here so hello everyone!

I am creating dashboard for hosipitals to help them with meeting demand for scanners (CT, MRI etc). It is already massive spreadsheet with lots of tabs and being under developemnt for months but now I am strugglig with one tiny(BIG!) issue that prevents me to complete the dashboard.

1614184391743.png


I hope you can see above screenshot if not I attached the file.
This is changed and simplified version of the actual work due to confidentiality (apologies) but should do the job.

My problem is:
Each machine works certain number of hours per quarter (which is displayed in row 4) and it varies depends on year. Each machine need to be replaced after reaching max level of hours (shown in cell H2). What I need is to sum hours until it will reach max capacity. As in example I inputed data manually but apart from column A and B (marked in blue) rest should be automated. What I need is to find date for each machine to be replaced (Column C) and displayed it in columns D-S (this part I can do on my own). As you can see most crucial part is to find that bloody replacement date, then I can continue with the rest.

Notes: Spreadsheet is formula based and while it is not a problem with extra helper rows/columns I would rather try to avoid VBAs.

As seen in machine2(M2 - Column B7) etc there are different start dates do sum must start counting beginning of that date, as in example - before "Q4 2021" it didn't calculated sum and started from that date (Q4 2021-Q4 2022).

Let me know if you have any questions.

Thank you for help in advance, if someone could find a way it would be very helpful.

Regards,
Ellmal
 

Attachments

  • image001.png
    image001.png
    21.5 KB · Views: 2
Last edited by a moderator:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,996
I'd add a new column between C & D and label D5 (after inserting) "Match".

After inserting that column:

Code:
D6 = MATCH(B6,$E$5:$T$5,0)
E6 =CHOOSE(2+SIGN(COLUMN()-COLUMN($D$5)-$D6),"","ok",IF(OR(D6="n/a",D6="R"),"n/a",IF((E$4+SUMIF(D6:$E6,"ok",D$4:$E$4))>$I$2,"R","ok")))
C6 =INDEX($E$5:$T$5,MATCH("R",E6:T6,0))

Then, copy C6:D6 down and copy E6 down and right.
 
Solution

Ellmal

New Member
Joined
Feb 24, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I'd add a new column between C & D and label D5 (after inserting) "Match".

After inserting that column:

Code:
D6 = MATCH(B6,$E$5:$T$5,0)
E6 =CHOOSE(2+SIGN(COLUMN()-COLUMN($D$5)-$D6),"","ok",IF(OR(D6="n/a",D6="R"),"n/a",IF((E$4+SUMIF(D6:$E6,"ok",D$4:$E$4))>$I$2,"R","ok")))
C6 =INDEX($E$5:$T$5,MATCH("R",E6:T6,0))

Then, copy C6:D6 down and copy E6 down and right.
Hi,

That worked like a charm! Thank you.

As a matter of fact it worked so great (they really loved it!), after showing it to management they want extra features, I've looked into the formula you made hoping I will be able to adjust, but it is too much for me.

I feel bad asking for more as you already helped me a lot but extras would be very helpful.

In short: they want to expand forecast by more years until 2036 which requires two replacements of one machine, let me ilustrate on the same example:
https://gm1.ggpht.com/kKwhXm5hqGtHO4bIH-CbqGkQGJWxKjnkOg5t2JLjXB5h_oPmbURBuvcC-NrGfM3_M83w4ooHpDd1bmmBTgtJ8ryjD3ns_uD87tIO1wpSgZCIKjH1YB3fFZahWFPwxDsIdYyLONmvRxzDbv40JKn0grNqQxtAqIhBLIrRChhO7D-r05VSPwFCa5MIHrusPirACjlL_8G8t9hFDB9uXLR_XE8Avf2rFJcH5_3fSHMBkxGwFlHplAzk4eCkq0cHv0asax5puRYUESKLLGEdtKqu537xZ6fRZCyifjNBFRD6B_WSTO7ldSG-d9KImIUhWwqmhS8AuTZofgso4oqOHt_EweGrBaF3ilD-Nu6_dWq65EWESHvKn8t37GeJF77hU5zN0p2Q4kwiZkhKBYCxHnR4WN4jJLFVdUkx2bGH6Jo6LYdCr6nT0G0LBEaLM-UnbtKqDdWyYc8WrfcdjxmPOpV71eHrXX5zFuEKnv0s5IpLCBGK0pUWoOsWvCrlzDgmOSaVw8QB-TQhJeUf7DsyHhFoIWR5b-cBeCS7nzfaIMyvRhHDcFhBMMGbg1zwqu3O9B1ehYOWEprD0nOBLNB7Df6_UiFSJbTu7-nt3Ypi8nZZOindABnz3EcJ0iaeTD4Hi9xxvduEjwW88Q4jtLmtbaYL5318jYgH-Z1V5rHQLYvPIqkWniLk07w3_V0CfQaMkmb2ZIXjVC7Lc0tR-P-7y-OkrcBuviftnWHDAGm-3R1cYo1vL72DtiBj10M1yvj5XR6HWHc8rA=s0-l75-ft-l75-ft


As you can see each machine still has the same capacity (in reality it is about 8 years), after reaching it and hitting replacement date ("R") it has two quarters of downtime ("D") and starting to work again ("ok") and after reaching same capacity again it need to be replaced again ("R"). Some machines will extend beyond the table time (in reality it is 2036) hence "Beyond Q4 2024". Column F is what you suggested as extra (which will be hidden in final version).

Again: extra columns/rows are welcome, even naming might be different: "ok", "R" etc. change to whatever suits best.

Thank you again for help, let me know if you have any questions.

Regards,
Ellmal
 

Ellmal

New Member
Joined
Feb 24, 2021
Messages
3
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Again: I dont know how to edit posts, apologies.
I can see there was an issue with image loading so please find attached it again (hopefully it will work this time)

unnamed.png
 

Attachments

  • image001.png
    image001.png
    21.5 KB · Views: 0

Watch MrExcel Video

Forum statistics

Threads
1,126,940
Messages
5,621,727
Members
415,853
Latest member
Newlife72

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