google-sheets

Using Google Sheets to calculate electricity bill with Different Tariff

enter image description here

This is the table that I use to calculate electricity bill, all I need is just edit the cell which highlighted in yellow (Usage per month). If the usage is above 600, the calculation is correct, but when the usage is less than 600, then something is wrong.

enter image description here

The cell which has a value of -100 should be zero.

This is my formula for the column.

enter image description here

enter image description here

enter image description here

enter image description here

enter image description here

Any idea how to solve this?

I tried MIN;MAX. but error.

Solution

  • SUGGESTION

    You can try this formula:

    =map(B5:B8, array_constrain(scan(C11, B4:B8, lambda(a,c,a-sum(c))), rows(B5:B8), 1), lambda(x, y, if(min(y,x)>0, min(y,x), 0)))

    Kindly clear all the values on cells C5:C8, and paste this formula on cell C5 only.

    OUTPUT

    enter image description here