Need help finding excel formula to subtract from balance only if text box is filled in

%3CLINGO-SUB%20id%3D%22lingo-sub-2650069%22%20slang%3D%22en-US%22%3ENeed%20help%20finding%20excel%20formula%20to%20subtract%20from%20balance%20only%20if%20text%20box%20is%20filled%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2650069%22%20slang%3D%22en-US%22%3E%3CP%3ECan%20someone%20help%20find%20the%20formula%20in%20my%20spreadsheet%20below.%26nbsp%3B%20I%20need%20column%20N%20%22Remaining%20Balance%22%20to%20calculate%20the%20balance%20from%20Column%20L%20%22Approved%20Amount%20Infusion%22%20minus%20Column%20S%20through%20V.%20But%20the%20formula%20needs%20to%20start%20over%20again%20when%20a%20different%20name%20appears%20in%20column%20A.%26nbsp%3B%20If%20there%20is%20a%20blank%20cell%20in%20column%20A%20then%20that%20should%20be%20a%20trigger%20to%20start%20the%20formula%20fresh%20in%20column%20N.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Dan_Shermannavectis_0-1629043100164.png%22%20style%3D%22width%3A%20722px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303376i90D1EA628B94AD70%2Fimage-dimensions%2F722x406%3Fv%3Dv2%22%20width%3D%22722%22%20height%3D%22406%22%20role%3D%22button%22%20title%3D%22Dan_Shermannavectis_0-1629043100164.png%22%20alt%3D%22Dan_Shermannavectis_0-1629043100164.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2650069%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2651083%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20finding%20excel%20formula%20to%20subtract%20from%20balance%20only%20if%20text%20box%20is%20filled%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2651083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1128904%22%20target%3D%22_blank%22%3E%40Dan_Shermannavectis%3C%2FA%3E%26nbsp%3BPersonally%2C%20I'm%20not%20really%20fond%20of%20having%20empty%20rows%20within%20tables%20and%20I%20don't%20understand%20why%20you%20have%20created%20a%20one%20column%20structured%20table%20in%20the%20middle%20of%20a%20larger%20%22regular%22%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%2C%20the%20following%20formula%20could%20do%20what%20you%20need%20if%20you%20get%20rid%20of%20the%20empty%20rows%20and%20structured%20table.%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E%3DIF(A5%26lt%3B%26gt%3B%22%22%2CIF(ISNUMBER(L5)%2CL5-SUM(S5%3AV5)%2CN4-SUM(S5%3AV5))%2C%22%22)%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20very%20elegant%2C%20but%20it%20works.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Can someone help develop a formula for my spreadsheet below.  I need column N "Remaining Balance" to calculate the balance from Column L "Approved Amount Infusion" minus Column S through V. But the formula needs to start over again when a different name appears in column A.  If there is a blank cell in column A then that should be a trigger to start the formula fresh the line below the blank column.

 

 

Dan_Shermannavectis_0-1629043100164.png

 

2 Replies

@Dan_Shermannavectis Personally, I'm not really fond of having empty rows within tables and I don't understand why you have created a one column structured table in the middle of a larger "regular" table.

 

But, the following formula could do what you need if you get rid of the empty rows and structured table.

=IF(A5<>"",IF(ISNUMBER(L5),L5-SUM(S5:V5),N4-SUM(S5:V5)),"")

 

Not very elegant, but it works. See attached.