need formula to work only if the cell is populated

**lisach** · 02-01-2012, 09:15 PM

I have 1 column that contains numbers from a counter. The next column will subtract yesterday's number from today's number giving a difference. Even though there is a cell for each day. The counters are not checked every day. Therefore some cells are blank. So I need the formula to then look above to find the next previous populated cell. Having a hard time figuring this out. Any help will greatly appreciated. I have attached a sheet.counters.xlsx

Thanks in advance

**MarvinP** · 02-01-2012, 09:54 PM

Hi Iisach,

Find the formula and your example attached with this formula in C4 and down.

=IF(B4=0,"",IF(AND(B4>0,B3>0),B4-B3,IF(AND(B4>0,B3=0,B2>0),B4-B2,IF(AND(B4>0,B3=0,B2,B1>0),B4-B1,"not yet"))))

**lisach** · 02-03-2012, 08:19 PM

Worked like a charm! Thanks! I would never have come up with this if i worked for the rest of the Year!

**lisach** · 02-17-2012, 07:17 PM

Hi Marvin,
You helped me with this formula a couple of weeks ago. I ran into a problem with it. Not sure what the issue is. As I said in the first post. The counters are not checked every day therefore some of the cells in column B may be unpopulated. I applied the formulas as you wrote them but found sometimes they don't work. To illustrate this. Open the sheet you attached above. delete the number in cell B6 as if the counter was not checked that day. The number displayed in cell C9 should then be 1428 but it displays the "not yet" message instead. Interestingly.... if the number in B9 is moved to.... say B7.... everything works.

**lisach** · 02-17-2012, 07:23 PM

Well I think I figured it out, the formula you wrote only goes back a few cells. I need the formula to check all the way back to B3 from C10 if necessary. If the counters were not checked all week long, Sometimes that happens. The day they are checked is never the same, it would need to get the starting counter from B3.