a formula to avoid manual typing

[snjpverma]

If my data is in cells A1 to A4, I want the same output as this below formula would give.

=MIN(0,A1-1*B1,A1+A2-2*B1,A1+A2+A3-3*B1,A1+A2+A3+A4-4*B1)

Please see the pattern of the RED highlighted part) i.e. first A1, then A1+A2, then A1+A2+A3 then A1+A2+A3+A4. it goes on upto A4. However, if the data is until A10 then it should gone until A10. So it all depends on the length of data in column A.

Also, the constant number to be subtracted is B1 as shown in blue above. B1 is multiplied by the numbers 1, 2, 3, 4, and so on.. (See the highlighted blue pattern (i.e. B1*1 then B1*2 then B1*3 and B1*4.
If the data in column A was upto A10, then the B1 also would have been multiplied until 10*B1

In short:
When it is just A1 then A1 - 1*B2
When it is A1+A2 then the B1 is multiplied by 2
When it is A1+A2+A3 then B1 is multiplied by 3
This is how the pattern is. Please advise a short formula for the same. This will reduce a lot of manual typing for me.
Thanks for all the help that is provided on this forum.

P.S: the Total of A1 to A15 is in cell A16. In case, that could be of any help in making a formula succinct..
Note: The data can go maximum up to A15 only. it will never be more than that.
Please let me know if any additional info is required

 

[NARAYANK991]

Hi ,

If your data is in cells A1 through A15 , where do you want the formula to be entered ?

Narayan

 

[snjpverma]

The formula to be entered is in cell O1

 

[bosco_yip]

This part :

A1-1*B1,A1+A2-2*B1,A1+A2+A3-3*B1,A1+A2+A3+A4-4*B1……..to A1+A2+...A15-15*B1

is equal to :

=SUM(OFFSET(A1,,,COUNT(A1:A15)),-COUNT(A1:A15)*B1)

Regards
Bosco

 

[snjpverma]

Thanks for your reply Bosco_yip. However, the formula isn't working for me.
Attached is the sheet in which I entered the provided formula and I also entered manually. Only the manual formula gave correct result.
The formula you gave only calculates the last part. for example if the data is until A15, the formula does this.
=Sum(A1:A15)-15*B1
So, it gives MIN function only one value i.e. the last value..

 

[snjpverma]

Let me give an example here.
If the data in column A is up to A3
the formula gives only the red part to Min function. i.e. the last possible value. The other values of MIN i.e. the pink and the green values are ignored.
=Min(A1-1*B1,A1+A2-2*B1,A1+A2+A3-3*B1)

Please let me know if it is still not clear.

 

[AliGW]

I don't understand where -3 comes from. I cannot get to that result from the manual formula. I think you need to explain in WORDS what you are trying to do.

 

[bosco_yip]

Thanks for your reply Bosco_yip. However, the formula isn't working for me.
Attached is the sheet in which I entered the provided formula and I also entered manually. Only the manual formula gave correct result.
The formula you gave only calculates the last part. for example if the data is until A15, the formula does this.
=Sum(A1:A15)-15*B1
So, it gives MIN function only one value i.e. the last value..

Try,

1] In D7, array formula (SHIFT+CTRL+ENTER) :

=MIN(0,SUBTOTAL(9,OFFSET(A$1,,,ROW(INDIRECT("1:"&COUNT(A$1:A$15)))))-B$1*ROW(INDIRECT("1:"&COUNT(A$1:A$15))))

2] See attachment

Regards
Bosco

 

[snjpverma]

BoscoYip, you nailed it.. The formula does exactly as intended.

ALIGW, the below manual formula gave me -3

=MIN(0,A1-1*B1,A1+A2-2*B1,A1+A2+A3-3*B1,A1+A2+A3+A4-4*B1)

 

[Peter Bartholomew]

If you name your first column, A, and the value you name, B, then the array formula
= MIN( SUMIF(A,"<="&A) - A*B )
should give the desired result.

If you wish to avoid the need for CSE then use a Named formula "V" to refer to the inner formula fragment
= SUMIF(A,"<="&A) - A*B
in which case the formula on the sheet reduces to
= MIN( V )

 

[bosco_yip]

If you name your first column, A, and the value you name, B, then the array formula
= MIN( SUMIF(A,"<="&A) - A*B )
should give the desired result.

If you wish to avoid the need for CSE then use a Named formula "V" to refer to the inner formula fragment
= SUMIF(A,"<="&A) - A*B
in which case the formula on the sheet reduces to
= MIN( V )

1] Try to test using the OP's Test sheet by other numbers.

2] A1: A4 enter : 4, 3, 2, 1, and B1 =3

3] Results :

OP's manual formula (Post #.9) result : -2

My formula (Post #.8) result : -2

Your formula (Post #.10) result : -3

Regards
Bosco

 

[Peter Bartholomew]

@Bosco
You are perfectly correct of course. I did not know the purpose of the calculation or what might change, so I kicked the process off with a minimal input! There are three number sequences implicit in the OP: the row number that determines the number of terms; the contents of column A and the hard-wired coefficients of B.
The most straightforward solution would be to insert a column 'k' containing the record index {1;2;3;4} which could double up as the row counter and the coefficient of B. The formula for V (can also be used within an array formula) becomes:
= SUMIFS( A, k,"<="&k ) - k*B