I have a very simple spreadsheet.

Col A Col B-Col F
Date #'s

I was not planning on implementing a formula. I was just using the sheet as a log.

After ~3 weeks of logging I decided to add a Col G that would average each row.

Problems:
1. Formatting
A. I didn't initially intend for the numbers to be stored as text even though I wasn't 'working' with them. Frankly, I thought that Excel handled a number entry as a number unless the cell was formatted otherwise? However, 98% of the cells have a comment indicator noting that the numbers are stored as text.

B. If I right click on the cell, Format, Number, Number, Okay--the 'stored as text' comment indicator remains even though I (thought obviously) just changed the format from Text to Number.

2. Calculation
A. I noticed the format problem when I attempted to Average the row of columns B-F. The Average result of the first row is incorrect. The sheet is averaging 5.0, 5.0, 4.5, 4.5 and 4.5 = 5.00. Further, whether I copy the average formula from row 1 to row 2 or manually insert another Average for row 2 it results in a #DIV/0! error.



B. I've tried reproducing the sheet in a separate area first formatting the area as Numbers, numbers but I still get the divide by 0 error. I don't get it. The cell containing the error formula is '=AVERAGE(B10:F10)'. Excel is supplying the divisor not me. There are no skipped or '0' value cells between B and F?

?

Mark

Can you upload the sheet?

One thing to try is to select all the numbers in one column then click Data - Text to columns- Finish ( you can skip the intermediate steps)
Same for the other columns
Be aware that text is always left aligned and numbers are right aligned in cells ( when no custom alignment is applied)
Now check if it works

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Here's a dupe of the sheet

Attached Files

Rx Stop.xlsx (16.8 KB, 9 views)

Put a 1 in for example G2.
Take a copy of G2.
Sect A3:F70.
Right click and Paste Special.
Check Multiply.
OK.

Select A3:A70.
Format as date.

It seems you fixed all the problems by copying the default cell format over the work area.

If correct, any clue as to what I could have done to have messed up the work area formatting so thoroughly and absent conscious effort?

Mark

No, it is not about formatting. It is about the fact that something which appears as numbers actually is text.
Try (for example in G6) to enter the formula: =ISNUMBER(D6) and i H6 the formula: =ISTEXT(D6). The first formula should show TRUE and the second FALSE. To convert all these 'text numbers' to actual numbers I multiplied by 1. Another way (just one cell at a time) is to select D6, press F2 and then press Enter.

I am not sure how this has come, but sometime it happens when importing data from other systems.

Got it--thanks.

Possibly pressing your helpfulness a bit--

For whatever reason I've always found Excel's graphing function wizards more confusing than helpful and just giving up.

This time the graph is just too simple.

If I wish Col A (time) to be the X axis and Col G (day average) the Y axis isn't there a simple way just to open a blank line graph and click on either axis and have a box open up (similar to that which happens when you choose to repeat a spreadsheet's top several rows) and click either the Col A or G series and for those values to then represent the axis data?

Mark

Select your time data in column A, hold down the Ctrl key and select your day average data in column G, click Insert and select your preferred line chart.

I don't know any easier way than that.

@markg2
Please don't ask multiple questions in the same thread. Members usually first look at the title, and don't necessarily read the entire thread, eventually missing valuable info provided by the answer to your second question

Start a new thread with every new question and if relevant add a link to your other thread(s)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

You're correct, I know better and understand.

Sorry for an inconvenience.

Mark

>>>Select your time data in column A>>>>

Thanks again--

MS needs to hire folks like you to write their help/wizards!

Alternatively, somehow make shortcut solutions a functional part of help in some (counter to MS' ability) intuitive manner.

Mark