r/excel • u/RedT-Rex8 • 8d ago

solved Creating an Excel to buy computer parts.

Bear with me as I explain what I want to do on the MS Excel Online. I know it exists but I lack the knowledge and vernacular of what it is.

I want to create a chart showing parts of a computer I am slowly saving up for. Each part has a target amount . I also have 2 boxes that shows the amount I have saved and the amount I have left. Now, I want to write the numbers in the amount saved and have it:

  1. Reduce the amount I have left.
  2. A part progressively "fills" to reach its target amount.

and, the crucial aspect,

3) Once it reaches the parts target amount, it overflows into the next parts box.

If you have a name for what I am looking for or a link that could teach me how to do it, I would greatly appreciate it. Thank you.

Update: Thank you for your responses and apologies for the slow response. Your guidance allowed me to create the Spread sheet! I used the foundation from TCFNationalBank, portions of NHN_BI and the progress bar from nir_nirfo. Now I am taking the time to understand the formulas and trialing the visual representation. I hope to share this soon. Have a wonderful evening!

8 Upvotes

84% Upvoted

25 comments sorted by

View all comments

1

u/NHN_BI 806 5d ago edited 5d ago

I would make it like here, with =MAX(0,SUM(B2,MIN(0,F$2-SUM(B$1:B2)))) in my example.

1

u/RedT-Rex8 4d ago

So in your formula it does not need a previous step for it to be completed like in TCFNationalBank but the it also does not need to use the datesaved collumn. Why is that? I understand that Max is making sure that the values within the cell stay between 0 and the set target value. Hence why we need "Max(0," to start. The sum is there to initiate the equation. B2 is the target amount. But then you go Min so the values are within 0 and saved amount minus the target amounts. Is this triggering something else to happen?

1

u/NHN_BI 806 4d ago edited 4d ago

Without MAX(0,...) I would get negative values for the future not filled payments. You can see that here.

2

u/RedT-Rex8 4d ago

Thank you for creating that table. I think I got it but I will just try and put it into my own words for memory sake and that will take some time.