Excel Formulas and Error Messages

[compugoddess]

Hello everyone. I am new to this forum, so please bear with me as I try to explain myself. I have two similar problems with similar sheets.

I am working on a spreadsheet to calculate grades for clinical education. Column A is the list of modalities. Each modality has two rows - one for the range of dates "worked" in that modality, and one for the grade received (ie: 89.1). Columns B through G contain the dates and the grades. Column H is the average grade for that modality. At the bottom, in H45, I need to input the average of the modalities, which are weighted differently, so I cannot average column h. The average must be to 1 tenth (89.1) and cannot round up (88.15 is still recorded as 88.1). I have put the formula in to average the block of cells with the grades in it (B:G) and I used TRUNC, 1 to limit the rounding. However, when added manually on the calculator, the numbers are often off by 1 tenth (89.2 instead of 89.1). Any ideas?

In the same type of arrangement, I also have some sheets that need to have one cell that is the result of an average calculated into the overall average. However, not all of the students have a number in this cell, which results in the #DIV/0 error. I can hide this, but it still uses it in the calculation, which in turn gives me a #Value! error. Is there any way to write the formula that, for instance, averages (SUM(B10:G42 + d 42) but only if D42 contains a number and ignores it completely if it contains a 0 or error?

Thanks for any help!

 

[Dreamboat]

For the first part, you can use:

=ROUNDDOWN(AVERAGE....

For the second, use:

=SUMIF(B10:G42,">0",B10:G42)/COUNTIF(B10:G42,">0&quot

http://www.TheWordExpert.com

 

[SkipVought]

Hi, compu,

The answer to your second ? is easier than the first. You test the divisor in your formula...

=IF(B5=0,"",B4/B5)

The answer to your first ? is a question... Why did you make your spreadsheet so difficult? The data that you describe might be better organized...

MODALITY   DATE   GRADE

Organizing you data this way will make it easier to analyze and summarize using many of the built-in features of Excel like SubTotals and PivotTables.

Skip,
metzgsk@voughtaircraft.com

 

[JVFriederick]

Rounding problem.
Maybe try duplicating what you do on a calculator step by step and try to mimic this in the file.
Do you clear the memory and reenter a result, or does the calculator keep more precision than displayed?

I've had good experience leaving alone the rounding and truncating in formulas until the very last, but I'm not sure if you can do this.

Averages.
IF(D42,SUM(B10:G42)/D42),0) will test for blanks and zeros

HOWEVER - I think your formula should probably read
IF(D42,SUM(B10:B42)/D42),0) or
IF(D42,SUM(B$10:B$42)/D42),0)
Looks like you will otherwise have a circular reference.

Sorry not much help on this one.

 

[compugoddess]

Thanks to all that replied. I will try those out posthaste.

The only problem is that for line 42, the ONLY cell I want included is D42, the rest of the rows i want to include all of B through G.


The spreadsheet can't be "simple" for several reasons:

1. Each modality has several date ranges (and none have a set number - there can be as few as three or as many as 6) which sets the "weight" for that modality.

2. This is the format the program director wants it in and no one can tell her otherwise (we've tried). I'm trying to improve what can't be seen.

Thanks again!

 

[SkipVought]

You know that there is a BIG difference between what is seen and what really is.

Data that is well organized if often summarized and presented in an entirely DIFFERENT format. Trying to work in a presentation format as opposed to a database format is asking for trouble -- the kind that you are attemting to deal with in this thread.

Anyhow, you can tell your director that this IT professional says that she ought to stick to her directing and allow you to do the things that will get her the RESULTS that she needs.

Here's an example of some data...

MODALITY          DATE          GRADE
    1          03/03/02          98.6
    1          03/03/02          87.5
    1          03/10/02          84.3
    1          03/10/02          88.0
    1          03/10/02          93.9
    2          03/03/02          94.3
    2          03/03/02          86.2
    2          03/03/02          89.2
    2          03/03/02          79.9
    2          03/10/02          97.1
    2          03/10/02          87.0
    2          03/10/02          99.6

Now here's a PivotTable report showing average grades by modality by date...

Average of GRADE                    
MODALITY          DATE          Total
    1            3/3/02          93.1
                3/10/02          88.7
    1 Total                      90.5
    2            3/3/02          87.4
                3/10/02          94.6
    2 Total                      90.5
Grand Total                      90.5

Now here's a PivotTable report showing average grades by date by modality...

Average of GRADE                    
DATE          MODALITY          Total
3/3/02           1               93.1
                 2               87.4
3/3/02 Total                     89.3
3/10/02          1               88.7
                 2               94.6
3/10/02 Total                    91.7
Grand Total                      90.5

I (blushing, but it was REALLY the PivotTable Wizard) created this in less than 2 minutes. AND there is so much more that is buit into Excel that can be used IF YOUR DATA IS ORGANIZED PROPERLY.

I dismount from my soapbox

Hope some of this helps

Skip,
metzgsk@voughtaircraft.com

 

[compugoddess]

Skip,

Not only was that a wonderfully helpful response, but it also brought a much-needed smile to my face - something that isn't seen very often around here when I'm working on this! Thank you!!

Now if only you could wave your magic wand and get back a corrupted Word document... LOL...

Thanks again,

Di

 

[JVFriederick]

Skip - You are absolutely right!!

110%

And then some...........

 

[Dreamboat]

Did I hear corrupted Word document? I always offer to give it a shot.

Dreamboat@TheWordExpert.com

Or...what tells you that it's corrupt? Have you read the FAQ here on file corruption?

http://www.TheWordExpert.com