increase a Value of a Cell depending on its value

[AdamSalam]

I have one special case, I want to increase some values (deduction from a fixed value), that's the number will be subtracted each time increase the value in question.

For example I want to increase any number from 45 - 49 to 50, from a balance of 10. so If I increase 46 to 50 I still have only the value of 6 ...

I f I increased another number of the same series, say from 44 up to 50, I have no more values to use.

Another criteria is that I want to start from the GREATER numbers and go down to less values, for example If I have three numbers of 43 , 45, and 48 I start increasing the number 48 then 45 then 43 as long as I still have balance from the limited 10 value

I tried to use SUMIFS function but I couldn't figure out how to control all of that specially how to decrease the value of 10.

please find the sample data with some clarification included within the worksheet

as well as I paste it here in case the attachment didn't work

All values below 46 need to be upgraded 50 Max
All values in a row can be increased by 10 total
we start increasing the greater number and then the next until we reach a total 10 points
term1 mid yr term2
55 65 66
42 53 34
31 31 42
54 45 42

hope I could clear the situation so I get your help

thank you in advance​

 

[tdh777]

Hi Adam,

When you say "increase the values in a row" do you actually mean change those values, or do you mean "put new values next to/(or Under) the 3 existing values with the new values" ?

If the first, you would have to use Visual Basic (macro or function) to get the result.

If the second then it could be done with 3 extra columns and 3 hidden columns all with appropriate formulas.

However, VBA (a Function or Macro) would probably be easier because of your rule to start with the highest value in the row.

Also, you keep talking about numbers under 46 but your sample data includes values in the 30s, 50s, 60s etc.

Do you mean "All the values in a row go up to the next highest multiple of 10 if possible" ?, OR
Do you mean "Only values ending with 5-9 go up to the next highest 10 if possible" ?

And when you say "If I have 3 values".. do you mean 3 values in the same row ? 3 values in the same column ? or 3 values in each group of 10 (30s, 40s, 50s, 60s etc) in the whole table ?

Sorry but your post isn't quite clear.

If you could post the desired RESULT for the sample date you have posted then I can help you get that outcome.

Cheers.
Warren K.

 

[AdamSalam]

thank you Warren very much for your reply,
sorry for confusing, let me explain
as you said above the case is Change the value
say from 46 to 50 maximum
every student Total score can be added 10 point (bonus)Maximum, this 10 points divided between the three terms but start with the Greater score,
say a student score 40, 45, 43: we add 5 points to the 45 it will be 50, then we still have 5 (from the 10) that should be added to the 43 and it well be 48 in turn, now we have no more points to add, we keep the score of 40 as it is .....

 

[AdamSalam]

Unfortunately, I couldn't attache a sample ..
but here is a screenshot share from on Google drive ...

 

[AdamSalam]

here is a screenshot share for the sample on Google drive ...
https://drive.google.com/file/d/1KPENQZrPG8dYN9R-8KV_tjZjMVDgcaSn/view?usp=sharing

 

[tdh777]

Hi Adam.
Thanks for the screenshot.

It answers some questions but not all, especially as you have some numbers in your screenshot that are NOT marked red (for changing), while others in the same range ARE marked red.

And i understood all the other "rules" to be applied... you didn't need to say those again... it's the inconsistency of information provided that is the challenge.

So please answer the following questions exactly and I will be able to help you.

1. When the process runs, should it ONLY affect values under 50 ? YES/NO.

2. What about values under 30 ? (Also see question 3 below). (Your sample appears to only show changes to values from 31-49)

3. If the answer to 1) is YES, why are values 45 (Cell C8), 34 (B11), 45 (D11), 36 (C12) NOT marked in Red ? Why would these values also not be expected to change following the other rules ?

	B	C	D
4	Term 1	Mid Year	Term 2
5	55	65	66
6	42	53	34
7	31	31	42
8	54	45	42
9	66	51	18
10	66	74	36
11	34	18	45
12	42	36	39
13	42	45	45
14	44	39	51
15	83	81	74
16	60	61	18

<tbody>
</tbody>

Once I have that information I can help you.

 

[AdamSalam]

Great


1. When the process runs, should it ONLY affect values under 50 ?
YES, precisely any value from 45 and above up to 49, can be increased up to 50

2. What about values under 30 ? (Also see question 3 below). (Your sample appears to only show changes to values from 31-49)
Only values from 45 to 49 , Don't care about the RED color

3. If the answer to 1) is YES, why are values 45 (Cell C8), 34 (B11), 45 (D11), 36 (C12) NOT marked in Red ? Why would these values also not be expected to change following the other rules ?

Please Ignore[FONT=&quot] the [/FONT]RED color its only for me to highlight the score for students that have low marks.
some of them deserve increasing but not all.

	B	C	D
4	Term 1	Mid Year	Term 2
5	55	65	66
6	42	53	34
7	31	31	42
8	54	45	42
9	66	51	18
10	66	74	36
11	34	18	45
12	42	36	39
13	42	45	45
14	44	39	51
15	83	81	74
16	60	61	18

<tbody>
</tbody>

Appreciate your valuable help

 

[tdh777]

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.

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 :

	B	C	D
4	Term 1	Mid Year	Term 2
5	55	65	66
6	42	53	34
7	31	31	42
8	54	45	42
9	66	51	18
10	66	74	36
11	34	18	46
12	42	36	39
13	45	47	46
14	44	39	51
15	83	81	74
16	60	61	18

<tbody>
</tbody>

AFTER :

	B	C	D
4	Term 1	Mid Year	Term 2
5	55	65	66
6	42	53	34
7	31	31	42
8	54	50	42	Y
9	66	51	18
10	66	74	36
11	34	18	50	Y
12	42	36	39
13	48	50	50	Y
14	44	39	51
15	83	81	74
16	60	61	18

<tbody>
</tbody>

Happy dodgy-marking-practices !

Warren K.

 

[AdamSalam]

Thank you Warren very very much for your endeavors to help ...


I am sorry for this delay, my computer (Windows 8) refused to boot today morning, I am thinking togo for Linux...!


I am using my daughter's computer for this post ...


Actually I am a freelance IT Trainer, I am working for myself 100% ...

this work is for a friend of mine he is a teacher and I want to help him solve some problems and make his life less nightmare ... otherwise he need to do everything manually ... I will keep your code intact only for customization porpuses for the copyright


I will try the macro and get back to you sooner God willing,


Appreciate your help

 

[AdamSalam]

Hi Warren,

Sorry for not receiving any reply from me yet, I am still trying to find solution to recover my data - my backup corrupted as well - and setup my computer ...