replacing lowest test grade with final if final is higher formula help

Hello all,
I am putting together a gradebook and am having problems coming up with an IF statement to replace the lowest, non zero test grade with the final.

If A1:A4 are the test and A5 is is the final, what would I need to do for the formula to look at the lowest, non zero test of those 4 and if the final is larger, replace it?

Thanks

Formulas can't replace data, only VBA can do that.

If you'd like some help with a macro, click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Keep in mind we're good at Excel, we're not good at "Colby thinking". Your example shows 100s all the way across, not very helpful. Also, your abbreviations aren't all obvious. Make it easy on us.

Please complete 2-3 complete rows of sample varying data. Don't dumb it down, make it as complex as it is normally and then show your expected results. Demonstrate the calculations for each row and explain why it is so it's clear.

Use the cells out to the right to show the progressions of your calculations until you reach a final grade. I'm sure we can help consolidate that all back down into one formula.

It looks like you already have the trick for dropping the lowest

JBeaucaire, that cracked me up. It makes perfect sense to me, but then again, I am the one that created it, so it would only be natural...

I have added more explanation in the form of text boxes to the example.

I am just stuck on how to get the lowest test grade dropped and replaced by the final if it is higher. I could have everyone enter it manually, but I know there has to be a way to do it automatically.

Once again, thanks for any help

Here's another way to approach it. Instead of thinking of it as "replacing a lower test grade with the final grade", think of it a s bonus of the difference.

Lowest test grade = 60
Final = 85
Bonus = 25

Put this formula in AF18 and copy down to see what I mean:
=IF(AE18>MIN(Z18:AC18),AE18-MIN(Z18:AC18),0)

This gives you the "bonus" points to add back in. Now apply your weights to that bonus as well and you should be good to go.

We are a village!

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

That's a tougher creature, requires an array formula, since the range being evaluated is small, this should be ok. Large-range-arrays can really slow your workbook down.

Put this array formula in AF18:

=IF(AE18>MIN(IF(Z18:AC18>0,Z18:AC18,"")),AE18-MIN(IF(Z18:AC18>0,Z18:AC18,"")),0)

...and confirm it by pressing CTRL-SHIFT-ENTER to activate the array. You should see your first value appear. If you get an error message, press F2, the try CSE again.

Once you get the first value to appear, you can copy that cell down the column.