I have a list of numbers such as follows:
1W
.5W
1W
1W
.25W
.25W
.5W
I need to add them up so I come up with 4.5W(in this example). Thanks.
I have a list of numbers such as follows:
1W
.5W
1W
1W
.25W
.25W
.5W
I need to add them up so I come up with 4.5W(in this example). Thanks.
Assuming there is always 1 character at the end, this will work:
=SUM(--LEFT(L20:L26,LEN(L20:L26)-1))&RIGHT(L20,1)
and use CTRL-SHIFT-ENTER rather than just ENTER.
I'm afraid I'm a bit lost. Where does this go? I don't really understand what all this is supposed to be. There will always be a W at the end of it.
Hi,
just for your example
=SUM(--LEFT(A1:A7,LEN(A1:A7)-1))&"W"
holding down Ctrl+Shift keys and Press Enter
hope it helps
it was already there, sorry
This is a formula you can put in any cell you like. Were you looking for a cell formula or VBA code?
Here is a small spreadsheet with the formula in it.
the only problem is that these are for an exact amount of cells, not a variable. I'm not sure how long the list is.
Try this updated version of the spreadsheet. It assumes that there is no data below the column of numbers with the "W"s.
Hi Guys!
I may be missing something here, but why not just format the cells with the text to "add" the "W" following a number input alone?
What I mean is that the input in the cell will be "1", ".5" etc and not "1W", ".5W" etc.
On the Format>Cells>Number Tab, select Custom and in the Type: input box type #.##"W".
In the cell for the total, SUM(A1:??) will then add the numbers because they ARE numbers simply formatted to show the "W".
I must add, though, that I learned a lot from working through your solutions!
Kindest regards
Harry
The problem (#2) is...
The W has to be there. I'm extracting info from another program and the W needs to be seen with the #. The previous solution worked. This isn't something I'm normally going to be doing anyway, so it's not too important as to how it gets done. Thanks for all the help everyone.
Don’t have an account yet? Register yourself now and be a part of our community!