Linking cells in excel with a specific format

I am trying to link cells in excel on two different work sheets. I am using the formula eg: cell1 = cell2 + cell3. The numbers that I have in cell2 and cell3 are in format of 100% (1) and 50% (2). I just want to add numbers 1 and 2 so that my cell1 will have number 3. Is it possible to d开发者_JAVA百科o without changing the cell formats? Thanks a lot.

If you don't care about the percentages, just copy your column with the percentages and change the format of that column to value and in sheet 2, do addition on that column instead.

Unfortunately when a cell has a format of percentage and a user enters a number, it is converted into what it means given the context of the format. It's not like what is being displayed is wildly different than what is 'hidden' inside the cell. When you reformat a cell, that data is reformatted as well, so 50% becomes .5 even if you had originally entered 50 in the cell before changing it's format. Format is more than just 'display format' so maybe that's where the confusion is.

If you want to add the cells in the percentage row and not bother with reformatting the formula cell you can cheat and treat it as a string to get rid of that %. You could do =Left(A2+A3, Len(A2+A3)) that will give you the 1.5 answer without having to format te cell.

Not sure i understood your question but i'll give some elements:

• formula and formats are separated in Excel, thus, you can set a formula in A1, say =A2+A3 but displays the value the way you wish
• for instance, if A2 contains 100% and A3 contains 50%, then the result in A1 is worth 1.5
• you can set the format of A1 the way you wish (Right-clic > Format cells > Number tab), for instance, decimal, the cell will then display 1.5 but if you choose percentage, the cell will then display 150%

Please elaborate your question if needed.

[EDIT] New answer thanks to your comment:
If i understand well, you want to sum up the values between brackets in your cell (whatever is before, event percentages in your case).
Then, you can try this in cell A3:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1 )+MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1 )