Excel formula problem

[paw]

Hello all,

This is a formula and error message issue and I am at my wits end.

I am putting in some test grades and trying to get the average of each test (or column). The average formula has worked up to the last 3 columns. Now I am getting this message: #DIV.0! Everybody didn't take every quiz so some cells are empty. But the cells that worked have empty cells in their rows also. I have formatted the cells as numbers and also as general. Still these last 3 columns don't work. Does anyone have any ideas. I would be most greatful.

Thanx

paw

 

[Dreamboat]

Formula please?

Anne Troy

http://www.VBAExpress.com

 

[paw]

Thanks for replying Dreamboat. =average(L4:L50)

 

[Dreamboat]

See if this helps:

=SUMIF(A:A,">0&quot/COUNTIF(A:A,">0&quot

Anne Troy

http://www.VBAExpress.com

 

[Dreamboat]

Sorry, Paw. Should have explained...

Greater than zero will also mean if there's a LETTER in the cell. So you'd better be sure all cells are really cleared, and formatted (as number, zero decimal probably) before you start.

Anne Troy

http://www.VBAExpress.com

 

[Zathras]

Assuming you are using the

 =AVERAGE(range)

function, then all of your data are either blank or text, not really numbers.

To test this hypothesis, try replacing

 AVERAGE

with

 SUM

and see what happens. (Result will probably be zero.)

Try re-entering the grades as pure numbers.

 

[paw]

thanks for the replys. I will try each one and let you know what happens.

Thanks again,

paw

 

[paw]

I tried Zathras suggestion first and sure enough the sum came up zeros. I don't understand when I have formatted the cells as numbers.

Thanks for your help. I will re-enter.

paw

 

[Dreamboat]

PAW:

The quick and dirty workaround to force Excel to see numbers AS NUMBERS and not as text is called "The Times One Fix"

Put a 1 in any blank cell.
Copy the cell.
Select all the cells with the baddie numbers.
Hit Edit-Paste Special, multiply, ok.

Anne Troy

http://www.VBAExpress.com

 

[paw]

Thanks Dreamboat. I will give your workaround a try, but not today. I have had it with this workbook

Thanks

paw

 

[Zathras]

Formatting cells as numbers doesn't make them numbers. Formatting only affects the way cell contents are displayed. Use Dreamboat's time-honored solution and you should be ok.

 

[Dreamboat]

Thanks for the backup, Zath.

I just want to clarify for Paw and other readers...

If you have more than a few cells, use the Times One Fix. It will do the same thing as if you retyped the entries. If only one or two cells are "misbehaving", then simply retype those values. It's quicker.



Anne Troy

http://www.VBAExpress.com

 

[xlbo]

Just as an addendum to this - in XP, there is an "error checking" option in Tools>Options. Under this tab, there are tick boxes, One of which is "Number Stored As Text"
If you tick this, it will highlight any numbers that are actually text (there will be a little green "comment" mark in the top left of the cell)

If you highlight a range of cells that show these errors, a little box will pop up to the side of the cell with a ! in the middle. Left click on this and you will get a range of options - one of which is "convert to number". Choose that and it will do the same as the "Times 1 fix"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Dreamboat]

Sweet, Geoff.
I should be using XP (or even 2003) by now, but I just hate all that extra crap!
(Stomps foot on ground in childish rage.)

Anne Troy

http://www.VBAExpress.com

 

[paw]

Goodmorning,

to everyone whose been helping me. I tried the times one and it didn't help either. I think something is wrong with my file. It's corrupt or something. At the end of the columns I'm having trouble with when I click sum it tries to sum up the row instead of the column so I have to type in the locations to sum up. Also when I print it, it prints out anywhere from 64 to 100 something pages and it's only 1 page. Maybe this file can't be helped and will have to be typed in totally from scratch.

Do you have any thoughts or is there something else I can do to determine what's wrong with those 3 columns.

Thanks again for your help.

 

[Dreamboat]

Send the file, Paw. I've got a little time this afternoon.

Anne@VBAExpress.com

Anne Troy

http://www.VBAExpress.com

 

[paw]

It's will be on the way in a few minutes.

thanx

paw

 

[xlbo]

I know what you mean Anne - 1st thing I did when I got XP'd was to set the colours back to windows classic and to change all the menus to just appear rather than "slide" or "fade". M$ new tag line should be
"XP - the OS for people who don't like computers"

plus excel crashes about 3 times more regularly on XP than it ever did on NT (97) :-(

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Dreamboat]

Excel crashes?? Not for me.

PAW: Got your file. Hang tight.

Anne Troy

http://www.VBAExpress.com

 

[Dreamboat]

Ok. Start by cleaning up your file according to the following (Pasted):

Cannot shift non-blank cells off of the worksheet error . . .
-or-
Excel prints extra blank pages . . .
This occurs when Excel believes that all of your rows or columns are filled. If you constantly use a file over and over, then delete the contents of cells by hitting your delete key, eventually you'll get this error message. You may have deleted the cell's contents, but the cells still contain formatting information. This can also cause file bloating.

Go to your first completely blank column to the right of your data. Hold your Shift and Ctrl keys and hit your right arrow key until it hits column IV. From the menu hit EditàClearàAll. Go to the first completely blank row at the bottom of your data. Hold your Shift and Ctrl keys and hit your down arrow key until it his row 65536. Hit EditàClearàAll. Save the file now, because it will not recognize the clearing of those cells until you do.

Got a DR appt in 15 mins. Be back shortly.

Anne Troy

http://www.VBAExpress.com