Total percentage equals 100% after deleting a column.

[Monica S G]

Hi Team,

What formula is used when any of the column is deleted from the sheet and that equal to 100%?

For example:

C1 C2 C3 C4 C5 C6 C7
15% 10% 15% 20% 30% 10% = 100% (Sum is 100%)

If C3 and C5 column us deleted, it should automatically consider 100% in the C7 column. Please provide the insights on the same.

 

[Monica S G]

Team,

Can you please let me know on priority? Thank you.

 

[SanjayGMusafir]

Hi Team,

What formula is used when any of the column is deleted from the sheet and that equal to 100%?

For example:

C1 C2 C3 C4 C5 C6 C7
15% 10% 15% 20% 30% 10% = 100% (Sum is 100%)

If C3 and C5 column us deleted, it should automatically consider 100% in the C7 column. Please provide the insights on the same.

Can't comment until see sample data

The details you are sharing all belong to one column "C" and different rows

Share a smple data using XL2BB to have better understanding of situation and your requirement

 

[Monica S G]

Can't comment until see sample data

The details you are sharing all belong to one column "C" and different rows

Share a smple data using XL2BB to have better understanding of situation and your requirement

I do not have any sample data. It is for my research purpose. If any one of the column is deleted, how does it divide among the columns and give us the 100% result at the end.

 

[SanjayGMusafir]

I do not have any sample data. It is for my research purpose. If any one of the column is deleted, how does it divide among the columns and give us the 100% result at the end.

See either total can be a formula based on data
or
data can be formula based on a fixed total

Excel can't keep both as interdependent formulae - That's Circular Reference (Error) in Excel

Hope it clarifies the situation

Still any help you need, feel free to ask

 

[Monica S G]

I have used =SUM($C$1:$C$5) formula to make it whole sum of 100% but when deleted on the column it automatically gives less than 100%. Is there any work around that works 100% even deleted column?

 

[SanjayGMusafir]

I have used =SUM($C$1:$C$5) formula to make it whole sum of 100% but when deleted on the column it automatically gives less than 100%. Is there any work around that works 100% even deleted column?

Are you looking for this solution? Try & Revert

=Let(GT,SUM($C$1:$C$7),Ifs(GT=100,GT,True,100))

OR

=Max(SUM($C$1:$C$7),100)

 

[Monica S G]

Are you looking for this solution? Try & Revert

=Let(GT,SUM($C$1:$C$7),Ifs(GT=100,GT,True,100))

OR

=Max(SUM($C$1:$C$7),100)

Sure, I will try. Thank you.

 

[Monica S G]

It will be equal to 100 and not 100%, one more to add here is it will not change the percentage of all other column and it remain AS_IS. How I want it to work is below:

10% 15% 10% 15% 25% 25% = 100%

when 15% is deleted, it should spread equally between other 5 columns and total should be 100% something that 15% will spread equally or unequally among them. Can you please suggest anything?

 

[Monica S G]

attached image for your reference.