rounding up problems

[nhush]

I want my rounding formula to change from {=SUM(ROUND(H6:K6,1))} to {=SUM(ROUND(H6:K6,0))} or vica versa depending on certain scores.

I have outlined the problem using two sets of student scores:

Using {=SUM(ROUND(H6:K6,0))}

Mid Final H/w Att Total Grade
37 37 10 10 94 A
37 37 10 10 94 A

When decimal points are shown with {=SUM(ROUND(H6:K6,0))}

Mid Final H/w Att Total Grade
37.4 37.4 10 10 94 A 0.4's rounded down to 0's
36.6 36.6 10 10 94 A 0.6's rounded up to 1.0's

When decimal points are shown with {=SUM(ROUND(H6:K6,1))} but actual total is a whole number

Mid Final H/w Att Total Grade
37.4 37.4 10 10 95 A+ 0.4 + 0.4 = 0.8 so rounded up to 1.0
36.6 36.6 10 10 93 A 0.6 + 0.6 = 1.2 so rounded down to 1.0

When all totals are whole numbers using {=SUM(ROUND(H6:K6,1))}

Mid Final H/w Att Total Grade
37 37 10 10 95 A+ 0.4 + 0.4 = 0.8 so rounded up to 1.0
37 37 10 10 93 A 0.6 + 0.6 = 1.2 so rounded down to 1.0

The example immediately above is the most accurate way to deal with this, however, now the four columns do not add up to the total, but the two students receive the correct letter grade.

Is there a way for Excel to identify a score like those above and automatically use either a .....(H6:K6,0) or ......(H6:K6,1)?

[vlady]

check first this one

=round(sum(....
with your
=sum(round(....

[nhush]

This doesn't work = 'too few arguments"

[vlady]

sorry my mistake i mean't..

from your formula {=SUM(ROUND(H6:K6,0))}

make it

{=round(sum(H6:K6,0))}

round then sum is different from sum then round..

[nhush]

I'm still getting the "you've entered too few arguments for this function' message with Ctrl,Shift,Enter =round(sum(H6:K6,0))

[vlady]

ok i think i'm hungry...
I misplaced the round decimal places 0

=round(sum(H6:K6),0)

[nhush]

Thank...finally...it worked!

Now I have the problem of the four columns not matching the total! Is there any way to solve this?

[nhush]

Could you help me with then next problem? It's a bit more tricky....

See attachment - worksheet 'totals' Column Z.

My formula is

=IF(AND(RANK.EQ(X6,X$6:X$25,0)>ROUND(COUNT(X$6:X$25)*0.3,0),LEFT(Y6,1)="A"),"B+",Y6)

It successfully deals with A grades over the 30% limit but it doesn't know what to do with the blank cells - these are students that have dropped out of school. I need to to take the 30% from the remaining number of students.

[vlady]

your first question... this makes them all equal to 94

=IF(SUM(IF(H6:K6=INT(H6:K6),1,))>0,SUM(ROUND(H6:K6,0))) - ctrl + shift + enter

your next query ( Rank.Eq) i can't do now have to go 12:00 PM here... maybe later this afternoon,, thanks