Excel formula and finishes

[NADWOA]

I have numbers in a column, with a sum total C11: C51 = 1, If I take the number is cell C16, I want the sum total to remain 1. so how can I do that.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[FDibbins]

Hi, welcome to the forum

Not really sure how you expect to have a range of values that sum to something, then remove a value from the range and expect the total to not change?

[NADWOA]

The sheet is not showing but if I take the number in Cell C16. the total value reduces to 86% = 0.86. I want the figures to adjust to get the total of 100% or 1

[FDibbins]

Not everyone can view images, and it is always better to upload a sample workbook.

Having said that, if you have an actual value in a cell, and you want that value to change to something else (for whatever reason), you cannot do that with a formula, you would need VBA or use a helper column. A cell can contain either data or a formula, not both

[NADWOA]

Thank you!

[FDibbins]

Soooo what did you do to sort this out?

[NADWOA]

I took those cells out. Worked my way back from the Grand total amount and finally got the 100% I was looking for.

[FDibbins]

Great stuff, thanks for sharing

Another way might be to have a sum for all the other cells, and then in that cell, =total-expected minus total

[NADWOA]

Thank you very much for your help

[FDibbins]

Happy to help