SOLVED

Formula to add 100 for every 0

%3CLINGO-SUB%20id%3D%22lingo-sub-3230975%22%20slang%3D%22en-US%22%3EFormula%20to%20add%20100%20for%20every%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3230975%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20cells%20F12-J12%20if%20any%20cells%20are%200%20need%20100%20for%20each%200%20to%20be%20added%20to%20L12.%20Any%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3230975%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-3232787%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20add%20100%20for%20every%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3232787%22%20slang%3D%22en-US%22%3EThank%20you!%20That%20worked%2C%20do%20you%20know%20how%20to%20combine%20the%20two%20formulas%20below%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3DL12-K12%3CBR%20%2F%3E%3DCOUNTIF(F12%3AJ12%2C0)*100%3C%2FLINGO-BODY%3E
New Contributor

In cells F12-J12 if any cells are 0 need 100 for each 0 to be added to L12. Any help would be greatly appreciated.

 

 

5 Replies
best response confirmed by CassandraMar (New Contributor)
Solution

@CassandraMar 

=COUNTIF(F12:J12,0)*100

Is this what you are looking for? 

Thank you! That worked, do you know how to combine the two formulas below?

=L12-K12
=COUNTIF(F12:J12,0)*100

@CassandraMar 

=COUNTIF(F12:J12,0)*100-K12

Maybe with this formula if i correctly understand what you want to do.

@Quadruple_Pawn 

 

I moved the formula over to M12, I need =COUNTIF(F12:J12,0)*100 and =L12-K12 to be in the same cell.

In this situation L12 is our beginning balance, K12 is our total paid and M12 is our ending balance. I need our total paid (K12) to be subtracted from the beginning balance (L12) and combined with the formula you gave me above in (M12) . For every unpaid week we will put a 0 which adds the 100 to the ending balance in M12 calculating the total unpaid balance including the 100 added for each 0 as well as total partial payments or full payments inputed in F12:J12. I have a formula in K12 giving the sum of F12:J12.

 

For example see the screenshot I have included. 

Screen Shot 2022-03-01 at 10.46.13 AM.png

@CassandraMar 

=L12+COUNTIF(F12:J12,0)*100-K12

You can try this formula in cell M12 which seems to work in my spreadsheet.