Skipping blank cells in simple math formula

[jimtmcdaniels]

Help, I need a formula for work that does the following,

In a 12 sheet excel workbook, each month of the year is represented in a
generic 31 row sheet. No data is entered in the rows representing weekends
or holidays our office is closed.

It's simple math concerning 3 cells, but the tricky part is one of the 3
cells the formula must use is in the previous working day's row. The problem
is I don't know how to tell the formula to look at the previous working day's
cell since on Monday's that would be 3 rows above and on the beginning of the
month, that would be the last cell-row with data in it on the previous
month's sheet, and then there's holidays too where rows are skipped-blank.

I.E. (formula is cell C2 =A2 -B2 +A1) In this example, Row 2 would
represent the 2nd of May. This formula works only if May 1st was a workday -
has data in it (cell A1). However if the 1st does not have data, because it
was a holiday, or weekend, then I need a smarter formula that knows to go to
the last business day's row, which in this case would be on a different sheet
in the workbook.

Thank-you for any help!

 

[K Dales]

Difficult problem. Could try to do in VBA but a worksheet solution might be
easier. Can you create a calculated column for your worksheets? If so, I
would make a column I would call "Last workday" or something like that. You
need to track what is the last workday's value for what is in column A so you
could use a formula like this one (written arbitrarily for the worksheet
"APRIL" cell D20):
=IF(ROW()=1,MARCH!$D$31,IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0)))
(explanation: if on row 1, will pick up the last workday value from the
prior month's sheet (row 31); if the value in A for "yesterday" is blank,
then it will look at the prior D value and copy this as the "last workday"
value, carrying it over on the weekend/holidays; if yesterday's A is not
blank then just use that. The ROW and OFFSET formulas are in there so the
same formula works for every cell in column D; this also means you can use it
for February and the months with 30 days to fill in the remaining rows of the
sheet carrying over the value so D31 always contains the last workday's value
from the month.

Hope that all makes sense, but I have tested it and it seems to handle the
situation. Use your D column value wherever you need the "last workday"
value from A. So, for example, the formula you gave becomes:
C2 = A2 - B2 + D2

 

[K Dales]

Hmm, a little too hasty! Correction needed on the part that looks at the
prior month:
=IF(ROW()=1,IF(MARCH!$A$31="",MARCH!$D$31,MARCH!$A$31),IF(OFFSET(A20,-1,0)="",OFFSET(D20,-1,0),OFFSET(A20,-1,0)))

 

[Tim Williams]

A UDF might be simpler to implement.

Eg:
'#####################################
Function DoCalc(a as range, b as range, c as range)

do while len(a.value)=0
set a=a.offset(-1,0)
loop

DoCalc=a+b+c

end if
'######################################

The disadvantage would be that if the "a" range is blank and it has to offset before calculating, then your function might not
refresh if the offset cell's value changes.
Adding "volatile" to the function should fix that (with a small cost in recalculation time)

 

[Tim Williams]

Crap - missed that part about the previous month. That would require a bit more coding to skip to the previous sheet if the value
of a.row got below a certain value.

 

[Tim Williams]

This works for me in a workbook set up with sequential monthly sheets.
It will keep skipping back in time until it finds a non-empty input for "a".
The rest of the calculation (last line) you should be able to adapt to suit.

Tim


'#####################################################
Function DoCalc(a As Range, b As Range, c As Range)
Const FIRST_ROW As Integer = 4
Const LAST_ROW As Integer = 34

Application.Volatile

Dim indx
indx = a.Parent.Index

Do While Len(a.Value) = 0

If a.Row > FIRST_ROW Then
Set a = a.Offset(-1, 0)
Else
If indx > 1 Then
indx = indx - 1
Set a = ThisWorkbook.Sheets(indx).Cells(LAST_ROW, a.Column)
Else
DoCalc = "No data!"
Exit Function
End If
End If

Loop

DoCalc = a & " " & b & " " & c

End Function
'######################################################