SOLVED
Home

Show Blank or Zero

%3CLINGO-SUB%20id%3D%22lingo-sub-1197490%22%20slang%3D%22en-US%22%3EShow%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197490%22%20slang%3D%22en-US%22%3E%3CP%3ECreating%20bank%20balance%20sheet.%20On%20row%20%2269%22%20column%20F%20is%20check%20or%20debit%20amount%20while%20G%20is%20deposit%20and%20H%20is%20the%20result%20of%20either%20the%20subtraction%20of%20F69%20from%20H68%20or%20the%20addition%20of%20G69%20to%20H68.%20I%20am%20using%20this%20formula%20in%20column%20H%20to%20get%20the%20result%26nbsp%3B%3DIF(F69%26gt%3B0%2CF69%2BH68%2CH68-G69)%20which%20works%20great%20until%20I%20copy%20it%20down%20past%20the%20rows%20that%20have%20entries%20in%20columns%20F%20and%20G.%20It%20repeats%20the%20last%20result%20in%20column%20H%20that%20had%20entries%20in%20F%20and%20G.%20I%20would%20like%26nbsp%3B%20to%20have%20it%20leave%20H%20blank%20or%20at%20least%20enter%20a%20zero%20(0)%20if%20there%20are%20no%20entries%20in%20F%20and%20G.%20Help%20would%20be%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1197490%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197547%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197547%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569103%22%20target%3D%22_blank%22%3E%40I_B_C%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20went%20at%20it%20a%20slightly%20different%20way.%20Here's%20the%20formula%20as%20I%20wrote%20it%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(AND(F69%3D%22%22%2CG69%3D%22%22)%2C%22%22%2CH68-F69%2BG69)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EYou%20can%20copy%20this%20formula%20down%20many%20rows.%20It%20basically%20is%20saying%2C%20if%20there's%20no%20entry%20in%20column%20F%20or%20G%2C%20then%20stay%20blank.%20If%20there's%20an%20entry%20in%20either%20(or%20both)%2C%20then%20take%20the%20running%20balance%20from%20the%20row%20above%20and%20subtract%20the%20debit%20amount%20(if%20any)%20and%20add%20the%20credit%20amount%20(if%20any).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197661%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BI%20can't%20believe%20that%20you%20were%20able%20to%20do%20that.%20I%20was%20totally%20unaware%20of%20that%20%22and%22%20function.%20Thank%20you%20so%20much.%20I_B_C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197678%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197678%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F569103%22%20target%3D%22_blank%22%3E%40I_B_C%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%2C%20you%20need%20to%20calculate%20a%20running%20balance%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Bank%20Balances.PNG%22%20style%3D%22width%3A%20436px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F173799i8360C4CFA030A6FD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Bank%20Balances.PNG%22%20alt%3D%22Bank%20Balances.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1197985%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1197985%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%20I%20am%20really%20glad%20to%20receive%20answer%20of%20my%20query%20of%20excel%20expense%20sheet.%26nbsp%3B%20%26nbsp%3BThank%20you%20very%20much%20dear%20for%20helping%20me%20out.%26nbsp%3B%20Ayub%20Khan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1199770%22%20slang%3D%22en-US%22%3ERe%3A%20Show%20Blank%20or%20Zero%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1199770%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20reply%20and%20it%20works%20very%20well%20to%20get%20a%20running%20total%20by%20copying%20your%20formula%20into%20the%20next%20line%20each%20time%20you%20enter%20new%20data%3B%20however%2C%20I%20wanted%20to%20copy%20and%20embed%20the%20formula%20to%20be%20ready%20each%20time%20I%20entered%20new%20data.%20Your%20suggestion%20produces%20the%20last%20number%20with%20new%20data%20all%20the%20way%20to%20the%20bottom.%20FYI%20I%20got%20this%20formula%20from%20another%20user%20which%20does%20what%20I%20needed.%26nbsp%3B%20%3DIF(AND(F8%3D%22%22%2CG8%3D%22%22)%2C%22%22%2CH7-F8%2BG8)%20F%20and%20G%20are%20the%20new%20numbers%20to%20be%20added%20or%20subtracted%20and%20H%20is%20the%20running%20balance.%20This%20allows%20me%20to%20copy%20the%20formula%20all%20the%20way%20down%20column%20H%20and%20will%20only%20produce%20a%20number%20in%20H%20when%20I%20have%20entered%20a%20new%20number%20in%20either%20F%20or%20G.%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F568485%22%20target%3D%22_blank%22%3E%40Ayub-Khan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Creating bank balance sheet. On row "69" column F is check or debit amount while G is deposit and H is the result of either the subtraction of F69 from H68 or the addition of G69 to H68. I am using this formula in column H to get the result =IF(F69>0,F69+H68,H68-G69) which works great until I copy it down past the rows that have entries in columns F and G. It repeats the last result in column H that had entries in F and G. I would like  to have it leave H blank or at least enter a zero (0) if there are no entries in F and G. Help would be much appreciated.

5 Replies
Highlighted
Solution

@I_B_C 

 

I went at it a slightly different way. Here's the formula as I wrote it:

=IF(AND(F69="",G69=""),"",H68-F69+G69)

You can copy this formula down many rows. It basically is saying, if there's no entry in column F or G, then stay blank. If there's an entry in either (or both), then take the running balance from the row above and subtract the debit amount (if any) and add the credit amount (if any).

Highlighted

@mathetes I can't believe that you were able to do that. I was totally unaware of that "and" function. Thank you so much. I_B_C

Highlighted

@I_B_C 

Perhaps, you need to calculate a running balance like this: 

Bank Balances.PNG

Highlighted

@Twifoo  I am really glad to receive answer of my query of excel expense sheet.   Thank you very much dear for helping me out.  Ayub Khan

Highlighted

Thanks for your reply and it works very well to get a running total by copying your formula into the next line each time you enter new data; however, I wanted to copy and embed the formula to be ready each time I entered new data. Your suggestion produces the last number with new data all the way to the bottom. FYI I got this formula from another user which does what I needed.  =IF(AND(F8="",G8=""),"",H7-F8+G8) F and G are the new numbers to be added or subtracted and H is the running balance. This allows me to copy the formula all the way down column H and will only produce a number in H when I have entered a new number in either F or G.  @Ayub-Khan 

Related Conversations
Conditional Formatting - Consider Only Value as Lowest Value
Ben Smith in Excel on
2 Replies
teams not showing office documents
Steven Davidson in Microsoft Teams on
2 Replies