Equal Sum Formulas 2

[justme152]

I was here a couple days ago with a problem and you guys worked it out.
=SUM(B4+C4+D4)/A4 =SUM(B4:D4)/A4
=sum(0+4+3)/12

it seem I had a number or two in text format instead of general format, so when I changed incorrect rows to general format the formula.
but now trying to transfer numbers from one worksheet to another and doing my formula again all I get is ##### instead of the percent
value. I have all the numbers in rows AA7 to AE14 in general format and the formula rows in percent format with zero decimal places but
for some reason the formula does not show the percent valve. both sample sheets are on a regular worksheet. but my workbook is saved
as a excel macro enabled workbook that has 5 worksheets I used to keep my records. but that should not matter should it.

I have included both worksheets samples. the one that was fixed the other day and sample 2 sheet.
what am I doing wrong on sample 2 worksheet.

[skywriter]

How did you transfer these numbers over? The reason I ask is that the problem is you have a space in each one of these cells after the number and that's what is causing the problem. For instance, choose your number 9 in cell AA7 of the sample sheet, then go to the formula bar and click into it and you'll see the cursor is 2 spaces to the right of the 9, if I try to remove these spaces and hit enter everything disappears. It's not the formatting of the cell, if I retype the number it works fine. If I simply copy and paste numbers from your other sheet they work fine.

[justme152]

skywriter, thanks for checking my problem out. not sure how to answer you question other then telling you I hit = from AA7 and went to the worksheet
in the workbook and went to row/column I needed. these are numbers downloaded daily.
but I see what you mean by coping the numbers from the 1st sample sheet to the 2nd sheet and the formula working. the numbers on the worksheet
I get the numbers are all in general format. how can I fix this format problem. everything on both sheet except formula rows are in genera format.

[skywriter]

Where do you get these numbers, you say you download them daily? I would guess there's something going on with these numbers when they are put into a spreadsheet. Explain what you mean when you say =AA7. What I specifically want to know is how you transferred the numbers into the sheet where the formulas didn't work. You say in the first post you transferred the numbers over, so my question is, what was your process. I highlighted numbers on your equalsum file and hit control + c to copy them then I chose AA7 of your sample sheet and hit control + v to paste and didn't have a problem. Something is going on and I'm trying to figure out what it is to give you a solution. The numbers on the sample sheet shouldn't have had spaces after them if you just copied them over.

[justme152]

I know what your asking and I'm trying to explain. I download numbers from a paid website, they are downloaded in text format. I install them daily to my
preset excel worksheet I click data from menu from across the top. click it on, click refresh then go to the folder the numbers are in high lite it click it and everything to same place on the worksheet every day. all the rows except the formula rows on the worksheet are in general format. formula rows are in percentage. I auto copy those numbers to a blank preset worksheet so not to mess up first worksheet. then I send them to the percent worksheet, if I can.

[skywriter]

You should read this:
https://support.office.com/en-au/art...1-c5bad0f0a885

Here's another, might be why I saw those spaces. You could do some further searching for spaces in imported numbers.
http://www.theexceladdict.com/_t/ebk56mft.htm

[justme152]

hey thanks skywriter. this may be what I'm looking for.

[skywriter]

I did some more research and here's what I discovered.
It appears it's not a space it's a tab character and using replace doesn't work, trim doesn't work, but I found what does.
=CLEAN(A1)+0, where A1 would be the cell with the number in question. So find a blank area and use this formula for the top left number in question and then copy it down and across until it references all the numbers. Then take the results of the formulas, copy them and then paste special "values" over the other numbers and they will be clean numbers.

So here's your spreadsheet as an example. See how over in the AM column I used the formula to clean all the numbers. I then copy all those cells with the formulas and I then choose the numbers with problems and I choose paste special Values and there yo go everything works. You can them delete the cells with the formulas.

Good Luck!!!