Sooooo... you are a teacher and you want help with "inflating" students' marks so that more students reach a "pass" mark ?
I certainly hope you are being well-paid for this activity !
Where's my cut ?
As previously mentioned since you are wanting to actually
change values in the cells rather than just display suggested new marks in a parallel table then you need a
macro to achieve the result, because it can't be done with formulas. (Formulas can't change the values of OTHER cells).
Finally, seeing as we don't want the marks inflated multiple times where someone accidentally runs the process again) I have used a simple mechanism to mark a row as "updated" so that it does not get updated again. The mechanism is the addition of a "Y" in the column next to the table ("E").
This mechanism means that you could for example run the macro, then add 200 new lines of results to the bottom of the sheet, then run the macro again and only the new lines would be processed. If you need to change the column (E) or the "processed" character (currently "Y") just change the two lines highlighted in BLUE in the code below.
When you run the Macro you will also see that it adds a little comment to any adjusted cells so that you can see what the original mark was. You can easily disable this feature if you don't want it.
Also note that it starts at Row 5 (as per your example) and then stops when it gets to a row with no value or a zero in Column B. If you have data where there might be an empty column B (Term 1) and values in the other columns you will need to adjust the stopping test "
While ActiveCell.Value > 0"
Here is the macro. Apologies that it is slightly longer than it needs to be , but the extra comments will help you (and potentially others) understand it and make changes if necessary in the future.
Rich (BB code):
Sub ImproveMarks()
' by Warren Kinny on 14-Mar-2018
' For MrExcel question
' www.mrexcel.com/forum/excel-questions/1046991-increase-value-cell-depending-its-value.html
Row = 5
Cells(Row, 2).Activate
Dim check_values As Variant
While ActiveCell.Value > 0
If ActiveCell.Offset(0, 3).Value <> "Y" Then
'** Only process Rows not already processed (no "Y" in Column E)
check_values = Range("B" & Row & ":D" & Row).Value ' Load Row values into an Array
'check_values = Range(ActiveCell, ActiveCell.Offset(0,2)).Value ' Alternate code to load values into Array
max_adjust = 10 ' Reset Maximum_adjustment for each Row
'** Now get rid of any values not eligible for adjustment.
For j = 1 To 3
If check_values(1, j) < 45 Or check_values(1, j) > 50 Then
check_values(1, j) = 0
End If
Next j
'** Check up to 3 values for eligibility for improvement.
'** Work from the highest down.
For j = 1 To 3
highest = Application.WorksheetFunction.Max(check_values)
If highest = 0 Then
Exit For ' No more eligible values remain
End If
' Find the first column containing this value
which_col = Application.WorksheetFunction.Match(highest, check_values, 0)
' Technically the below if-test is not needed, as values outside this range
' are removed from the array in processing above.
' But it's a Belt-and-braces approach in case of future changes to the other code :-)
If highest >= 45 And highest <= 49 Then
add_amt = WorksheetFunction.Min(max_adjust, 50 - highest)
ActiveCell.Offset(0, which_col - 1).AddComment ("Original Mark : " & highest)
ActiveCell.Offset(0, which_col - 1).Value = ActiveCell.Offset(0, which_col - 1).Value + add_amt
max_adjust = max_adjust - add_amt
ActiveCell.Offset(0, 3).Value = "Y" '** Flag this row as Updated.
End If
check_values(1, which_col) = 0 '** So value in this column is not used again. :-)
Next j
End If
Row = Row + 1
ActiveCell.Offset(1, 0).Activate ' Move down to next row
Wend
End Sub
Sample results. BEFORE :
[TABLE="width: 222"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Term 1[/TD]
[TD="align: center"]Mid Year[/TD]
[TD="align: center"]Term 2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]66[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]34[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]42[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]42[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]18[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]36[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]46[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]39[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]46[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]51[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]74[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]18[/TD]
[/TR]
</tbody>[/TABLE]
AFTER :
[TABLE="width: 286"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Term 1[/TD]
[TD="align: center"]Mid Year[/TD]
[TD="align: center"]Term 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]
50[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]
50[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]42[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]
48[/TD]
[TD="align: center"]
50[/TD]
[TD="align: center"]
50[/TD]
[TD="align: center"]Y[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]51[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]83[/TD]
[TD="align: center"]81[/TD]
[TD="align: center"]74[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]61[/TD]
[TD="align: center"]18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Happy dodgy-marking-practices !
Warren K.