limiting a returned value?

[jb88ci]

I'm having trouble figuring this one out.

I'm putting together a grades database. We have a policy that if a student gets less than 70% on an exam they can rewrite it. Their final mark will then be the average of the first attempt and second attempt, not to exceed 70%

In the listed formula, f13 is the first attempt, g13 is the retest. I want H13 to either show f13(if the value is greater than 70) or if F13 is <70% show the average of F13,13 not to exceed 70%.

It does what i want except limit the displayed average

=IF(F13<0.7,AVERAGE(F13,G13),F13)

thanks in advance.

[Cutter]

You need the MIN() function in there

=IF(F13<0.7,MIN(.7,AVERAGE(F13,G13)),F13)


So - how cold is it there?

[NBVC]

Try

=IF(F13<0.7,MIN(0.7,AVERAGE(F13,G13)),F13)

[jb88ci]

Yesterday was -35C with the windchill, base temp was about -25. Warmer today, about -20

[jb88ci]

Thanks a bunch, that did the trick.

[Cutter]

You're welcome. Don't forget to mark your thread as SOLVED. (see FAQ in menu bar at top of forum page for directions).