-2

Column H to total down column L, using the next cell where it comes across a deficit, and populating what amount it used to make up for the deficit in the last cell

I tried this:

=MIN(SUM($H$2:H2), 172)

but it does not use the next column to fix deficit.

I'd like it to look like this after the formula has been typed in

L Garcia
  • 9
  • 1

1 Answers1

0
=IF(SUM($H$2:H2)-172<0,SUM($H$2:H2),IF((172-SUM($H$2:H2)+H1)>=0,ABS(172-SUM($H$2:H2)),""))

Keep in mind that H1 is used so it shouldn't be numeric.

Result:

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • is there a way to edit this formula that if column K says "No" skip to next cell in column L and start calculating over again using the previous cells amount? So instead of it saying 16 for L3, it would be blank and L4 would be 16 =IF(SUM($H$2:H2)-172<0,SUM($H$2:H2),IF((172-SUM($H$2:H2)+H1)>=0,ABS(172-SUM($H$2:H2)),"")) – L Garcia Jul 20 '23 at 19:22
  • Something like this? =IF(SUMIF($K$2:K2,"Yes",$H$2:H2)-172<0,SUMIF($K$2:K2,"Yes",$H$2:H2),IF((172-SUMIF($K$2:K2,"Yes",$H$2:H2)+IF(K2="Yes",H1,0))>=0,ABS(172-SUMIF($K$2:K2,"Yes",$H$2:H2)),"")) – user11222393 Jul 20 '23 at 19:47