Forum Discussion

CS1516's avatar
CS1516
Copper Contributor
Nov 09, 2021

IF/AND Formula help

I have a spreadsheet which is used daily for scheduled/completed jobs. Basically, we have a scheduled row for each day of the week and how many jobs are planned for that day, and the second row shows how many jobs have been completed on each day of the week.

I want to create a formula that divides to give a % completion for each day, but if 0 jobs are planned an 2 are completed to show 200% for that day. I can do this successfully, but have been asked that if 0 jobs are scheduled and 0 are completed can it show 100% not 0% (For graphing reasons). I can't figure out a formula that will do all 3, divide the fields, show 100% if both 0 and show ###% if jobs are completed on a day 0 are scheduled.

 

Anyone know if/how this could be possible please

2 Replies

  • CS1516 

    Let's say the number of scheduled jobs is in B2 and the number of completed jobs is in C2.

    Use the formula =IF(B2=0,IF(C2=0,1,C2),C2/B2)

    Format the cell with the formula as a percentage. It can be filled down if required.