Replacing lowest score that is at least 70

[updiliman]

Say I have 4 quizzes, and there is an optional Review Session. Student gets either 100 or 0 for attending/not attending the review session. If student attends the review, their lowest quiz scores will be replaced by 100, provided the quiz to be replaced is at least 70. Please note that there could be duplicates in the quiz scores, replacing the first instance is enough.

Example:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Orig Q1[/TD]
[TD]Orig Q2[/TD]
[TD]Orig Q3[/TD]
[TD]Orig Q4[/TD]
[TD]Review Session (0 or 100 only)[/TD]
[TD]New Q1[/TD]
[TD]New Q2[/TD]
[TD]New Q3[/TD]
[TD]New Q4[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]70[/TD]
[TD]80[/TD]
[TD]90[/TD]
[TD]100[/TD]
[TD]20[/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]80[/TD]
[TD]30[/TD]
[TD]95[/TD]
[TD]100[/TD]
[TD]100[/TD]
[TD]80[/TD]
[TD]30[/TD]
[TD]95[/TD]
[/TR]
</tbody>[/TABLE]

In the 1st example, Q2=70 is the lowest score (should only consider quizzes that are at least 70), so it is replaced by 100.
In the 2nd example, 80 is the lowest score (that is at least 70), but there are repeated scores, only the first instance is to be replaced by 100.
If Review session = 0, then No change.

Thanks so much in advance !

 

[Peter_SSs]

Welcome to the MrExcel board!

If you have a recent Excel version with the MINIFS function then try this, copied across and down.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Orig Q1	Orig Q2	Orig Q3	Orig Q4	Review	New Q1	New Q2	New Q3	New Q4
2	20	70	80	90	100	20	100	80	90
3	80	80	30	95	100	100	80	30	95
Replace

If you don't have MINIFS but at least Excel 2010 then use this instead.

=IF(AND($E2=100,A2=AGGREGATE(15,6,$A2:$D2/($A2:$D2>=70),1),COUNTIF($A2:A2,A2)=1),100,A2)


If your version is even older (or you need this to work on all Excel versions) then use this. It is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.
{=IF(AND($E2=100,A2=MIN(IF($A2:$D2>=70,$A2:$D2)),COUNTIF($A2:A2,A2)=1),100,A2)}

 

[updiliman]

it worked, Thank you so much !!!

 

[Peter_SSs]

it worked, Thank you so much !!!

You're welcome. Thanks for letting us know.

 

[updiliman]

I have a problem with this formula,

If there are 2 quiz scores that are 0, it replaces the 0 by 100, we dont want this because 0 is not >=70. How to fix this ? Thanks so much

 

[Peter_SSs]

I have a problem with this formula,

I provided three formulas, which one are you referring to?

 

[updiliman]

I provided three formulas, which one are you referring to?

First one, with MINIFS

 

[Peter_SSs]

OK, try

=IF(AND(MAX($A2:$D2)>=70,$E2=100,A2=MINIFS($A2:$D2,$A2:$D2,">=70"),COUNTIF($A2:A2,A2)=1),100,A2)

 

[updiliman]

OK, try

=IF(AND(MAX($A2:$D2)>=70,$E2=100,A2=MINIFS($A2:$D2,$A2:$D2,">=70"),COUNTIF($A2:A2,A2)=1),100,A2)

Thanks ! It's perfect now.

 

[Peter_SSs]

Cheers.