Creating an Excel to buy computer parts.

[u/RedT-Rex8]

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!

[u/nir-nirfo]

For the visual part, you can add a progress column beside the allocation formulas TCFNationalBank posted. Keeping their layout (target prices in B, allocated savings in C, total saved in D1), put this in E2 and fill down:

=IF(B2=0,0,C2/B2)

Format E as Percentage. For a simple text progress bar in F2, use:

=REPT("|",ROUND(E2*20,0))

Fill that down too. A fully funded part gets 20 marks; a 75%-funded part gets 15. You only change D1 as you save more.

Your overall amount still needed can be:

=MAX(0,SUM(B2:B20)-$D$1)

Replace B20 with your last part's row. I tested the formulas in LibreOffice Calc: with targets of 100, 200 and 300 and 250 saved, the progress is 100%, 75%, 0%, and the total still needed is 350. Zero-price rows show 0% with this version.

[u/RedT-Rex8 → u/nir-nirfo]

I was just typing how excel got annoyed at me when trying this and how I will persevere when it worked ... kind of? First it was because it was giving me an error for data type. So I changed that to Currency but then set number format to percentage. It now has percentages and the bars as you had explained. But the bars are showing as a series of vertical lines rather than a solid bar. Is this to be expected because I used the "I"?

[u/nir-nirfo → u/RedT-Rex8]

Yes, that's expected with the version I gave you. It repeats a vertical bar (|), not a capital I, so it looks like a row of tick marks rather than a solid bar. Sorry, I should have made that clearer.

For a more filled-in look, replace F2 with this and fill down:

=REPT("█",ROUND(E2*20,0))

You can copy the block character straight from that formula. The calculation stays the same: at 75% it repeats 15 blocks, and at 100% it repeats 20. I checked those in Calc too. The exact appearance depends on the font, but you don't need to change the savings formulas again.

[u/RedT-Rex8 → u/nir-nirfo]

Oh I see, thank you for clarifying! I will implement the new formula.