Formula to add 100 for every 0

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)



Is this what you are looking for? 

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




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



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



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