Excel 2007 : Assignment Help? Averages not coming out correct in an "if" function...

**lsrh0009** · 04-10-2011, 04:41 PM

Hi everyone,

I have an assignment for college where I'm completing a scenario in which the professor has students do 5 tests; if they complete all 5 then you drop the lowest mark and calculate the average, if not then you simply calculate the average.

I have written this function but it doesn't seem very successful since the averages are turning out to be 200 and 300...which is obviously incorrect if the highest score you can get is 100!:

=IF(COUNT(B4:F4)=5,SUM(B4:F4-(MIN(B4:F4))/4,AVERAGE(B4:F4))

I put the parentheses around the MIN because I thought it would affect the order of operations, but no luck. Does anyone have any suggestions? It is greatly appreciated!

**abousetta** · 04-10-2011, 04:50 PM

Try this slight modification:

Please Login or Register  to view this content.

abousetta

**lsrh0009** · 04-10-2011, 05:03 PM

Ahhh! Thank you very much! I've been slowly driving myself crazy for a few hours doing this so I'm very grateful. I'm new to nested functions. Hopefully next time I can do it on my own.

**abousetta** · 04-10-2011, 05:10 PM

Glad I could be of help. It takes a while to get the hang of it and sometimes its good to calculate each part on its own until you figure out what's going on. That's sort of what I did to resolve this problem.

If you are satisfied with results, please mark the thread as [SOLVED] by editing your original post.

Thanks.

abousetta