Need help writing a MAX IF function

[excel_noob14]

I am a professed excel noob and so I need help writing this function. I need to write a function where a column of values will subtract from an original value until the value reaches zero.

If my original value is 125, I need the following table to look like this

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]B1[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B4[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B6[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]B7[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]

I need the function to be generic so that I can start with any original value and the function will subtract subtract 20 from that value until the value reaches less than 20 in the final cell.

hope this makes sense.

 

[Marcílio_Lobão]

excel_noob14, Good morning.

Scenario:
A1 --> TOTAL VALUE
B1 --> FIRST STEP

Try to use:
B2:B20 (adapt it to your reality)

formula -->

=IF(AND($A$1="",$B$1=""),"",MIN($B$1,$A$1-SUM($B$1:B1)))

Copy it down as necessary.

Is that what you want?

I hope it helps.

 

[excel_noob14]

Thanks for the reply. You could break down the formula a bit more for me. Like i said im a very much a beginner. What values do I replace the "" with?
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If my table is like that with my original value of 125 and the values in the B column that I want, could you lay out the formula a bit more for me. Appreciate it

 

[excel_noob14]

Thanks for the reply. You could break down the formula a bit more for me. Like i said im a very much a beginner. What values do I replace the "" with?
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]125[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



If my table is like that with my original value of 125 and the values in the B column that I want, could you lay out the formula a bit more for me. Appreciate it

 

[Marcílio_Lobão]

excel_noob14, Good afternoon.

The first part of the formula means: If cell A1 AND cell B1 are empty then everything is empty.

In other words, do not do any calculations.
This is to avoid showing wrong calculations.

By the way, I will perfect the formula I sent you.
Instead of "AND" we will put "OR" in the formula.
So if either of the two cells is blank the calculation will not be done.

Let's also put a condition that if the result is zero or negative, that is, it has exceeded the established limit, the cell will be empty as well.
This helps in the aesthetic aspect of not filling the zeros cells.

In your first message you showed that the first value would be in cell B1.
The formula was created considering that the first value without formula would be in cell B1. (Reread my first message).

In your current drawing shows that you have nothing in cell B1 and the value is in cell B2.

Then in cell B2 you will enter the initial value(constant) and the formulas should be placed from B3 to wherever you want.

Now follows, the new formula considering all these observations of this message:

B3 -->

=IF(OR($A$1="",$B$2=""),"",IF(MIN($B$2,$A$1-SUM($B$2:B2))<=0,"",MIN($B$2,$A$1-SUM($B$2:B2))))

Copy it down as necessary.

I hope it helps.

 

[Dossfm0q]

Good Day Try this

THX

Public Sub subtract()
Dim N As Long, R As Long, Q As Long
Dim L As Single
'N = 125' or
N = Range("A1")
S = 20 ' subtract Number
srw = 1 ' start row in column "B"
U = (N / S)
 Q = Application.Quotient(N, S)
  If N > (Q * S) Then Q = Q + 1
  Cells(srw, 2).Resize(Q + 2, 1).Clear 'clear Cell an One below more
    For R = 1 To Q
        L = N - ((R - 1) * S)
        If L >= S Then
            Cells(R + srw, 2) = S
                ElseIf L < S Then
             Cells(R + srw, 2) = L
        End If
    Next
End Sub