0
409
VistasMS Excel: how to sum scientific notation excluding bracketted variance values looking like X.XXE-01 (XX.XXE-01)?
I have cells of data in excel formatted as follows:
There is a need to have both the number and bracketted number in the same cell.
I wish to create four more cells containing the percentage contribution of each cell to the sum of the non-backetted numbers.
I.e the sum in the first new cell would need to be 3e-05/(3e-05 + 1.6e-05 + 6.02e-05 + 5.71e-04) x 100. Rounding issues do not matter.
I have limited experience in excel, and help would be very appreciated.
1 Respuestas
Responde la pregunta0
If you have limited experience in excel I strongly suggest you to use helper columns so at least you know what are you doing. If you just ask for a magic superformula, any issue that may appear will block you and you won't be able to resolve it.
Column B got a formula to extract the non-bracket numbers (note my decimal separator is the comma, I guess yours is the dot). Also, because my Excel is spanish, my argument separator is ; but probably yours is a comma:
=VALUE(LEFT(A1;SEARCH(" ";A1)-1))
Same for column C but for bracketted numbers:
=VALUE(SUBSTITUTE(SUBSTITUTE(RIGHT(A1;LEN(A1)-SEARCH(" ";A1));"(";"");")";""))
Then cell B6 is just a sum up of numbers from column B and cell B7 is the calculus you said in your post:
=B6/(B1*100)
You can do all this without helper columns and a really complex formula, like I did in column D. It's an array formula so you must introduce it pressing CTRL+SHIFT+ENTER:
=SUM(VALUE(LEFT($A$1:$A$4;SEARCH(" ";$A$1:$A$4)-1)))/(VALUE(LEFT(A1;SEARCH(" ";A1)-1))*100)
Anyways, I've uploaded the file to Gdrive so you can check the formulas (remember my decimal separator is the comma, not the dot, you'll need to replace commas manually)