How to sum each row and make other operations with each of the final value in google sheets?-CodePudding

I am trying to get the sum of the bottles in each row and multiple the sum with the price ($2) and subtract a discount ($0.50) every time the sum reaches a multiple of 3 using:

={"Total Price";arrayformula(if(len(B2:B),mmult(n(B2:D),transpose(sign(column(B1:D1)))),))}

Is there a way to do it?

CodePudding user response：

You can try expanding your formula to compute the discounted price:

Assume x = mmult(n(B2:D),transpose(sign(column(B1:D1))), your computation will be:

2*x - floor(x/3)/2

And your expanded formula will be:

={"Total Price";arrayformula(if(len(B2:B),2*mmult(n(B2:D),transpose(sign(column(B1:D1))))-floor(mmult(n(B2:D),transpose(sign(column(B1:D1))))/3)/2,))}

Sample Output:

CodePudding user response：

It seems to me that this is really a math question. That said, as I understand your goal, try this in F1:

={"Total Price";arrayformula(if(len(B2:B),(mmult(n(B2:D),transpose(sign(column(B1:D1))))*2)-(int((mmult(n(B2:D),transpose(sign(column(B1:D1))))*2)/3)*0.5),))}